RSS Feed

DATEPART()


DATEPART()

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™.

datepartAbbreviations
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw
hourhh
minutemi, n
secondss, s
millisecondms

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]