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

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]