DATENAME Function - SQL Server Date Time Functions

DATENAME function returns a character string representing the specified datepart of the specified date. This function returns a nvarchar value. The return value depends on the language environment set by using "SET LANGUAGE".  DATENAME function can be used in the SELECT list, WHERE, GROUP BY, HAVING, ORDER BY.
Syntax

DATENAME ( datepart, date )

datepart	Specifies the part of the date that DATENAME will return
date	an expression that returns a date or datetime or datetimeoffset or datetime2 or smalldatetime or time or string in a valid date format value

The table below lists all valid datepart arguments

datepart	Abbreviations	Return Value
year	yy, yyyy	1840-9999
quarter	qq, q	1-4
month	mm, m	January,...December
dayofyear	dy, y	1-366
day	dd, d	1-31
week	wk, ww	1-53
weekday	dw, w	Sunday,..Saturday
hour	hh	0-23
minute	mi, n	0-59
second	ss, s	0-59
millisecond	ms	0-99(with precision of 2)
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	ISOWK, ISOWW	1-53

Examples

DECLARE @date DATETIME 
SET @date= '2019-08-27 16:28:42.013' 
 
SELECT @date AS CurrentDate;------------------------------2019-08-27 16:28:42.013

SELECT DATENAME(year,@date) AS 'Year';--------------------2019 
SELECT DATENAME(yy,@date) AS 'Year';----------------------2019 
SELECT DATENAME(yyyy,@date) AS 'Year';--------------------2019 

SELECT DATENAME(quarter,@date) AS 'Quarter';--------------3 
SELECT DATENAME(qq,@date) AS 'Quarter';-------------------3 
SELECT DATENAME(q,@date) AS 'Quarter';--------------------3 

SELECT DATENAME(month,@date) AS 'Month';------------------August 
SELECT DATENAME(mm,@date) AS 'Month';---------------------August
SELECT DATENAME(m,@date) AS 'Month';----------------------August 

SELECT DATENAME(dayofyear,@date) AS 'Dayofyear';----------239 
SELECT DATENAME(dy,@date) AS 'Dayofyear';-----------------239 
SELECT DATENAME(y,@date) AS 'Dayofyear';------------------239 

SELECT DATENAME(day,@date) AS 'Day';----------------------27 
SELECT DATENAME(dd,@date) AS 'Day';-----------------------27 
SELECT DATENAME(d,@date) AS 'Day';------------------------27 

SELECT DATENAME(week,@date) AS 'Week';--------------------35 
SELECT DATENAME(wk,@date) AS 'Week';----------------------35 
SELECT DATENAME(ww,@date) AS 'Week';----------------------35 

SELECT DATENAME(weekday,@date) AS 'Weekday';--------------Tuesday 
SELECT DATENAME(dw,@date) AS 'Weekday';-------------------Tuesday  
SELECT DATENAME(w,@date) AS 'Weekday';--------------------Tuesday  

SELECT DATENAME(hour,@date) AS 'Hour';--------------------16 
SELECT DATENAME(hh,@date) AS 'Hour';----------------------16 

SELECT DATENAME(minute,@date) AS 'Minute';----------------28 
SELECT DATENAME(mi,@date) AS 'Minute';--------------------28 
SELECT DATENAME(n,@date) AS 'Minute';---------------------28 

SELECT DATENAME(second,@date) AS 'Second';----------------42 
SELECT DATENAME(ss,@date) AS 'Second';--------------------42 
SELECT DATENAME(s,@date) AS 'Second';---------------------42 

SELECT DATENAME(millisecond,@date) AS 'Milli Second';-----13 
SELECT DATENAME(ms,@date) AS 'Milli Second';--------------13 

SELECT DATENAME(microsecond,@date) AS 'Micro Second';-----13333 
SELECT DATENAME(mcs,@date) AS 'Micro Second';-------------13333 

SELECT DATENAME(nanosecond,@date) AS 'Nano Second';-------13333333 
SELECT DATENAME(ns,@date) AS 'Nano Second';---------------13333333 

SELECT DATENAME(TZoffset,SYSDATETIMEOFFSET()) AS 'TZoffset';-- +05:30 
SELECT DATENAME(tz,SYSDATETIMEOFFSET()) AS 'TZoffset';-------- +05:30 

SELECT DATENAME(ISO_WEEK,@date) AS 'ISO_WEEK';-------------35
SELECT DATENAME(ISOWK,@date) AS 'ISO_WEEK';----------------35
SELECT DATENAME(ISOWW,@date) AS 'ISO_WEEK';----------------35

Read More

Get Column or parameter properties with COLUMNPROPERTY function - SQL Server

Overview
    COLUMNPROPERTY function returns the property information about the column or parameter. This function returns an integer value. It returns 1 for true, 0 for false, NULL for invalid input/value,  and -1 for XML or large value types for the precision property.
    Catalog Views and Information_schema objects are used to get a lot of information about the column or parameter info in a useful format. But some additional column level properties information is missing which you can get using the COLUMNPROPERTY function.
Catalog Views: sys.columns, sys.procedures, sys.parameters, sys.objects
INFORMATION_SCHEMA: information_schema.columns, information_schema.parameters

Syntax

COLUMNPROPERTY ( id , column , property )

ID	Identifier represents the table object ID or Procedure ID
Column	Column represents the name of the column or parameter
Property	Property represents pre-defined values for the property information

The following are available pre-defined values for property argument.

Value	Description
AllowsNull	Allows null values.
ColumnId	Column ID value corresponding to sys.columns.column_id.
FullTextTypeColumn	The TYPE COLUMN in the table holding the document type information of the column.
GeneratedAlwaysType	Is column value system-generated. Corresponds to sys.columns.generated_always_type
IsColumnSet	Column is a column set. For more information, see Use Column Sets.
IsComputed	Column is a computed column.
IsCursorType	Procedure parameter is of type CURSOR.
IsDeterministic	Column is deterministic. This property applies only to computed columns and view columns.
IsFulltextIndexed	Column is registered for full-text indexing.
IsHidden	Is column value system-generated. Corresponds to sys.columns.is_hidden
IsIdentity	Column uses the IDENTITY property.
IsIdNotForRepl	Column checks for the IDENTITY_INSERT setting.
IsIndexable	Column can be indexed.
IsOutParam	Procedure parameter is an output parameter.
IsPrecise	Column is precise. This property applies only to deterministic columns.
IsRowGuidCol	Column has the uniqueidentifier data type, and is defined with the ROWGUIDCOL property.
IsSparse	Column is a sparse column. For more information, see Use Sparse Columns.
IsSystemVerified	The Database Engine can verify the determinism and precision properties of the column. This property applies only to computed columns and columns of views.
IsXmlIndexable	The XML column can be used in an XML index.
Precision	Data type length of the column or parameter.
Scale	Scale for the column or parameter data type.
StatisticalSemantics	Column is enabled for semantic indexing.
SystemDataAccess	Column is derived from a function that accesses data in the system catalogs or virtual system tables of SQL Server. This property applies only to computed columns and columns of views.
UserDataAccess	Column is derived from a function that accesses data in user tables, including views and temporary tables, stored in the local instance of SQL Server. This property applies only to computed columns and columns of views.
UsesAnsiTrim	ANSI_PADDING was set ON at time of table creation. This property applies only to columns or parameters of type char or varchar.

Examples of COLUMNPROPERTY function

-- Get table and identity column names in adventureworks database
SELECT top 5
    OBJECT_NAME(id) as ObjectName, Name as Identity_Column
FROM syscolumns
WHERE COLUMNPROPERTY( id ,name, 'IsIdentity') = 1
ORDER BY 1,2

-- Returns the length of the column (Person.Person.Title)
SELECT COLUMNPROPERTY(OBJECT_ID('Person.Person'), 'Title','PRECISION') AS COL_LEN

Reference :  https://docs.microsoft.com/en-us/sql/t-sql/functions/columnproperty-transact-sql

Read More

Split comma separated string in a column / string to rows and convert into columns in SQL Server

This article is about how to split comma separated string value in a column or string variable value into multiple columns in SQL Server. Here delimiter can be any special character such as comma (,), hash (#), dollar ($), etc.,.

1. Split comma separated string value and convert into columns
2. Split comma separated string in a column and convert into columns

Split comma separated string value and convert into columns
For example, if you have the following string and if you want each of the value comma separated string into a separate column.

-- Input variable (comma seperated)
declare @strval varchar(45)='mahesh,satish,krishna,lakshmi,rakesh'; 
-- Drop temp table if exists
IF OBJECT_ID('tempdb..##strspilt') IS NOT NULL DROP TABLE ##strspilt;
-- Identifying the start and end positions of comma separated values (CTE, CHARINDEX)
-- spilt the string into rows using start and end position
-- final resultset will be copied into temp table (##strspilt)
;with str_spilt_col AS ( 
     SELECT 1 AS start_pos,
        CHARINDEX(',',@strval) AS end_pos,1 AS id
    UNION all
    SELECT CHARINDEX(',',@strval,end_pos)+1 AS start_pos,
        CASE
        WHEN CHARINDEX(',',@strval,end_pos+2)=0 THEN
        DATALENGTH(@strval)+1
        ELSE CHARINDEX(',',@strval,end_pos+2)
        END AS end_pos, id+1 AS id
    FROM str_spilt_col
    WHERE end_pos<=DATALENGTH(@strval)-charindex(',',REVERSE(@strval))+1 )
SELECT SUBSTRING(@strval,
        start_pos,
        end_pos-start_pos) AS msg,
        id into ##strspilt
FROM str_spilt_col 
-- convert rows to columns (DYNAMIC SQL)
-- Prepare dynamic query  
declare @sql nvarchar(500)='',@i int=1 
while (@i<= (SELECT max(id) FROM ##strspilt)) 
begin 
    if (@sql='') 
        SET @sql+='(select msg from ##strspilt where id='+cast(@i AS nvarchar)+') as col'+cast(@i AS nvarchar)
    ELSE 
        SET @sql+=',(select msg as col'+cast(@i AS nvarchar)+' from ##strspilt where id='+cast(@i AS nvarchar)+') as col'+cast(@i AS nvarchar) 
    SET @i+=1 
end; 
SET @sql='select distinct '+@sql 
execute (@sql)

Split comma separated string in a column and convert into columns
For example, if you have the following column (Name_age_gender_height_weight) and if you want each of the value comma separated string from that column into a separate column.

declare @strspilt table
(
  Name_age_gender_height_weight varchar(25)
)
insert into @strspilt values
('mahesh,31,M,178,75'),
('suresh,35,M,172,85'),
('sita,25,F,168,58')

select p1.Pos,p2.Pos,p3.Pos,p4.Pos,
substring(Name_age_gender_height_weight,1, P1.Pos - 1) as Name,
substring(Name_age_gender_height_weight, P1.Pos + 1, P2.Pos - P1.Pos - 1) as Age,
substring(Name_age_gender_height_weight, P2.Pos + 1, P3.Pos - P2.Pos - 1) as Gender,
substring(Name_age_gender_height_weight, P3.Pos + 1, P4.Pos - P3.Pos - 1) as Height,
substring(Name_age_gender_height_weight, P4.Pos + 1, 10) as [weight]
from @strspilt
  cross apply (select (charindex(',', Name_age_gender_height_weight))) as P1(Pos)
  cross apply (select (charindex(',', Name_age_gender_height_weight, P1.Pos+1))) as P2(Pos)
  cross apply (select (charindex(',', Name_age_gender_height_weight, P2.Pos+1))) as P3(Pos)
  cross apply (select (charindex(',', Name_age_gender_height_weight, P3.Pos+1))) as P4(Pos)

That’s it.Let me know if you have any questions, suggestions or comments.

Read More

Default ports used by SQL Server Services

Firewall systems help prevent unauthorized access to computer resources. If a firewall is turned on but not correctly configured, connections to SQL Server instances and related services will be blocked. So database administrators need to ensure that firewalls are properly configured to allow inbound and outbound traffic to the SQL Server instance.
Below table list the default ports used by SQL Server services.

Service	Port	Description
SQL Server Database Services	1433 1434 1433 or Dynamic Port	Default Instance Dedicated Admin Connection Named Instance (If named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433 otherwise it uses different port.UDP port 1434 might be required for the SQL Server Browser Service when you are using named instances.)
SQL Server Browser Service	UDP 1434 TCP 2382	Default
SQL Server Service Broker	TCP 4022	Default
SQL Server Analysis Services	TCP 2383	Default Instance. When using Named Instances, TCP port 2382 is needed.
SQL Server Integration Services	TCP 135	Default
SQL Server Reporting Services	TCP 80 TCP 443	Non-SSL (http://) SSL (https://)

Port 135

TCP port 135 is used for many services.

• Transact-SQL debugger
• Microsoft Distributed Transaction Coordinator (MS DTC)
• Windows Management Instrumentation (WMI) - SQL Server Configuration Manager uses WMI to list and manage services.
• Microsoft remote procedure calls (MS RPC) - used to start, stop, and control SQL Server Integration Services and is required only if you connect to a remote instance of the service from SSMS

View sys.tcp_endpoints helps to list ports used by various services such as Service Broker, Mirroring, etc.,

SELECT name, protocol_desc, port, state_desc FROM sys.tcp_endpoints WHERE type_desc in ('SERVICE_BROKER','DATABASE_MIRRORING')

For detailed information about ports used by SQL Server, refer Microsoft article
"Configure the Windows Firewall to Allow SQL Server Access"

Read More

SQL Server 2017 Graph Databases ( Overview, Uses, Example )

Overview
    Graph database is introduced in SQL Server 2017 version and it is a collection of Nodes and Edges. Both the relational and graph databases are data management system software. In Graph database building blocks are Nodes and Edges whereas in relational database building blocks are tables.

Read More

SQL Server Window Function - ROW_NUMBER() usage and examples

Overview

• ROW_NUMBER function returns a unique integer value starting with one and incremented by one for every row.
• The ROW_NUMBER function enumerates the rows in the sort order defined in the OVER clause.
• An ORDER BY expression inside the OVER clause is mandatory.
• Optional PARTITION BY can divide data into smaller groups based on the column.
• All the window functions that depend on ORDER BY in the OVER clause are non-deterministic. Refer Microsoft article on Deterministic and Nondeterministic Functions.
• ROW_NUMBER is only for output reference.

Syntax : 

ROW_NUMBER() 
OVER([PARTITION BY <col1>[,<col2>,...<n>]] ORDER BY <col3>[,<col4])

OVER: How the ranking should order or split the data

PARTITION BY: To split the data into groups/partition based on column

ORDER BY: Ordering of the data

Consider the below sample data:

CREATE TABLE EMPLOYEE(ENAME VARCHAR(45),EDEPART VARCHAR(45))
INSERT INTO EMPLOYEE(ENAME,EDEPART) 
VALUES 
('KRISHNA','IT'),('RAMKRISHNA','CIVIL'),('PRASAD','IT'),
('RAMESH','ACCOUNTS'),('MAHESH','MANAGEMENT'),('KALYAN','IT'),
('SATISH','ACCOUNTS'),('ASHOK','CIVIL'),('GANESH','MANAGEMENT')

ROW_NUMBER() Function without Partition By clause
Without Partition By clause SQL Server considered all records in the result-set as single partition or group and then ranking functions are applied.
Example

SELECT ROW_NUMBER()
    OVER (ORDER BY EDEPART,ENAME) AS ID,EDEPART,ENAME
FROM EMPLOYEE

Output:

ROW_NUMBER() Function with Partition By clause
With the PARTITION BY clause, a column or set of columns specified will divide the result set into record partitions. Then, finally ranking functions are applied to each record partition separately.
Example

SELECT ROW_NUMBER()
    OVER (PARTITION BY EDEPART
ORDER BY  EDEPART,ENAME) AS ID,EDEPART,ENAME
FROM EMPLOYEE

Output

Refer : https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
Let me know if you have any questions, suggestions or comments.

Read More

Different ways to find the Nth max salary in SQL Server

It is the most common interview question to be asked for SQL developers. Finding Nth highest value/salary in a table, where N could be 2, 3, 4 or anything.

Read More