DATEPART
Returns an integer representing the specified datepart of the specified date.
Syntax
DATEPART ( datepart , date )
Arguments
datepart
Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.
| 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 |
The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.
The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.
date
Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.
For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.
Return Types
int
Remarks
The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively.
Examples
The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE as well as DATEPART.
SELECT GETDATE() AS 'Current Date'
GO
Here is the result set:
Current Date
---------------------------
Feb 18 1998 11:46PM
SELECT DATEPART(month, GETDATE()) AS 'Month Number'
GO
Here is the result set:
Month Number
------------
2
This example assumes the date May 29.
SELECT DATEPART(month, GETDATE())
GO
Here is the result set:
-----------
5
(1 row(s) affected)
In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.
SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)
Here is the result set:
----- ------ ------
1 1 1900