How to Use the DATEPART() Function in SQL Server: A Complete Guide
☰Fullscreen
Table of Content:
Syntax:
<span class="pln"> </span><span class="typ">DatePart</span><span class="pun">(</span><span class="typ">DatePart</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Date</span><span class="pun">)</span><span class="pln"> </span>
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:
<span class="pln"> </span><span class="typ">Select</span><span class="pln"> DATEPART</span><span class="pun">(</span><span class="pln">weekday</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2012-08-30 19:45:31.793'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> returns </span><span class="lit">5</span><span class="pln"> </span><span class="typ">Select</span><span class="pln"> DATENAME</span><span class="pun">(</span><span class="pln">weekday</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2012-08-30 19:45:31.793'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> returns </span><span class="typ">Thursday</span><span class="pln"> </span>
Output:
The above code will produce the following result-
<span class="pln"> </span><span class="lit">5</span><span class="pln"> </span><span class="typ">Thursday</span><span class="pln"> </span>
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:
<span class="pln"> SELECT DATEPART</span><span class="pun">(</span><span class="pln">year</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2018-05-10'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">[</span><span class="pln">datepart</span><span class="pun">],</span><span class="pln"> DATENAME</span><span class="pun">(</span><span class="pln">year</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2018-05-10'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">[</span><span class="pln">datename</span><span class="pun">];</span><span class="pln"> </span>
The output looks the same:
<span class="pln"> datepart datename </span><span class="pun">-----------</span><span class="pln"> </span><span class="pun">-----------</span><span class="pln"> </span><span class="lit">2018</span><span class="pln"> </span><span class="lit">2018</span><span class="pln"> </span><span class="pun">(</span><span class="lit">1</span><span class="pln"> row affected</span><span class="pun">)</span><span class="pln"> </span>
Code:
<span class="pln"> SELECT DATEPART</span><span class="pun">(</span><span class="pln">year</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2018-05-10'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">'1'</span><span class="pln"> </span><span class="pun">[</span><span class="pln">datepart</span><span class="pun">],</span><span class="pln"> DATENAME</span><span class="pun">(</span><span class="pln">year</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2018-05-10'</span><span class="pun">)</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">'1'</span><span class="pln"> </span><span class="pun">[</span><span class="pln">datename</span><span class="pun">]</span><span class="pln"> </span><span class="pun">;</span><span class="pln"> </span>
Output:
The above code will produce the following result-
<span class="pln"> datepart datename </span><span class="pun">-----------</span><span class="pln"> </span><span class="pun">-----------</span><span class="pln"> </span><span class="lit">2019</span><span class="pln"> </span><span class="lit">20181</span><span class="pln"> </span><span class="pun">(</span><span class="lit">1</span><span class="pln"> row affected</span><span class="pun">)</span><span class="pln"> </span>
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).
No Questions Data Available.
No Program Data.