How to Use the DATEPART() Function in SQL Server: A Complete Guide

Syntax:

DatePart(DatePart, Date)

DatePart(DatePart, Date) - Returns an integer representing the specified DatePart. This function is simialar to DateName(). DateName() returns nvarchar, where as DatePart() returns an integer. The valid DatePart parameter values are shown below.

DatePart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	isowk, isoww

Code:

Select DATEPART(weekday, '2012-08-30 19:45:31.793') -- returns 5
Select DATENAME(weekday, '2012-08-30 19:45:31.793') -- returns Thursday

Output:

The above code will produce the following result-

5
Thursday

DATENAME() vs. DATEPART()

Note that DATENAME() is similar to the DATEPART() except for the return type. The DATENAME() function returns the date part as a character string whereas the DATEPART() returns the date part as an integer.

See the following example:

Code:

SELECT
    DATEPART(year, '2018-05-10') [datepart], 
    DATENAME(year, '2018-05-10') [datename];

The output looks the same:

datepart    datename
----------- -----------
2018        2018
 
(1 row affected)

Code:

SELECT
    DATEPART(year, '2018-05-10') + '1' [datepart], 
    DATENAME(year, '2018-05-10') + '1' [datename] ;

Output:

The above code will produce the following result-

datepart    datename
----------- -----------
2019        20181
 
(1 row affected)

Because the DATEPART() function returns an integer, the expression evaluates to 2019 (2018 + 1). However, the DATENAME() function returns a character string, therefore, the + is the concatenation operator which results in '20181' (2018 + 1).