Returns a single part of a date/time, for example year, month, day, hour, minute, etc.
Syntax:
DATEPART ( datepart ,date )
Examples of DATEPART()
SELECT DATEPART(year, GETDATE())
2010
SELECT DATEPART(month, GETDATE())
7
SELECT DATEPART(week, GETDATE())
28
SELECT DATEPART(day, GETDATE())
7
SELECT DATEPART(hour, GETDATE())
12
SELECT DATEPART(minute, GETDATE())
28
SELECT DATEPART(second, GETDATE())
55
SELECT DATEPART (yyyy,'2010-07-07');
2010
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.
In this below 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
[Source of Above Example]
No comments:
Post a Comment