How to Use the DATEPART() Function in SQL Server: A Complete Guide
Table of Content:
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).