DATEPART (TSQL Function)

Returns the specified part of the date, as integer value.

Syntax

DATEPART ( datepart , datetime_expression )

Arguments

datepart: part of the date to return.
See the list of valid values for datepart in the table below.
datetime_expression: is an expression of type date, time, datetime.

Return Types

Returns int.

Remarks

List of valid values for datepart:

datepart	Abbreviations	Result
year	yy , yyyy	[0001, 9999]
quarter	qq , q	[1, 4]
month	mm , m	[1, 12]
dayofyear	dy , y	[1, 366] leap year has 366 days
day	dd , d	[1, 31]
week	wk , ww	January 1st belongs to week 1. First day of week depends on the current LANGUAGE.
weekday	dw	[1, 7] First day of week depends on the current LANGUAGE.
hour	hh	[0, 23]
minute	mi , n	[0, 59]
second	ss , s	[0, 59]
millisecond	ms	[0, 999]
microsecond	mcs	[0, 999999]
nanosecond	ns	[0, 999999999]

Abbreviations can be used instead of the datepart full name. E.g. second, ss and s are equivalent.

For week and weekday, the result depends on the current setting for the first day of week. This setting can be modified with SET LANGUAGE or SET DATEFIRST.

Instead of using the dateparts year, month and day, you can just use the equivalent functions YEAR(date), MONTH(date), DAY(date).

Examples

DECLARE @dt datetime = '2007-10-30 12:15:32.123456789'

SELECT 'year',        DATEPART(year, @dt)
UNION ALL
SELECT 'quarter',     DATEPART(quarter, @dt)
UNION ALL
SELECT 'month',       DATEPART(month, @dt)
UNION ALL
SELECT 'dayofyear',   DATEPART(dayofyear, @dt)
UNION ALL
SELECT 'day',         DATEPART(day, @dt)
UNION ALL
SELECT 'week',        DATEPART(week, @dt)
UNION ALL
SELECT 'weekday',     DATEPART(weekday, @dt)
UNION ALL
SELECT 'hour',        DATEPART(hour, @dt)
UNION ALL
SELECT 'minute',      DATEPART(minute, @dt)
UNION ALL
SELECT 'second',      DATEPART(second, @dt)
UNION ALL
SELECT 'millisecond', DATEPART(millisecond, @dt)
UNION ALL
SELECT 'microsecond', DATEPART(microsecond, @dt)
UNION ALL
SELECT 'nanosecond',  DATEPART(nanosecond, @dt)

The result is:

datepart   |value      |
-----------+-----------+
year       |       2007|
quarter    |          4|
month      |         10|
dayofyear  |        303|
day        |         30|
week       |         44|
weekday    |          3|
hour       |         12|
minute     |         15|
second     |         32|
millisecond|        123|
microsecond|     123456|
nanosecond |  123456789|

Examples for week and weekday:

For week and weekday, the result depends on the current setting for the first day of week. This setting can be modified with SET LANGUAGE or SET DATEFIRST.

The following table shows results for various SET DATEFIRST settings.

DECLARE @d datetime = '2007-04-21'

PRINT '2007-01-01 is ' + DATENAME(weekday, '2007-01-01')
PRINT '2007-04-21 is ' + DATENAME(weekday, @d)

SET DATEFIRST 1
PRINT 1, DATEPART(week, @d), DATEPART(weekday, @d)
SET DATEFIRST 2
PRINT 2, DATEPART(week, @d), DATEPART(weekday, @d)
SET DATEFIRST 3
PRINT 3, DATEPART(week, @d), DATEPART(weekday, @d)
SET DATEFIRST 4
PRINT 4, DATEPART(week, @d), DATEPART(weekday, @d)
SET DATEFIRST 5
PRINT 5, DATEPART(week, @d), DATEPART(weekday, @d)
SET DATEFIRST 6
PRINT 6, DATEPART(week, @d), DATEPART(weekday, @d)
SET DATEFIRST 7
PRINT 7, DATEPART(week, @d), DATEPART(weekday, @d)

The result is:

2007-01-01 is Monday
2007-04-21 is Saturday

datefirst|       week|    weekday|
---------+-----------+-----------+
        1|         16|          6|
        2|         17|          5|
        3|         17|          4|
        4|         17|          3|
        5|         17|          2|
        6|         17|          1|
        7|         16|          7|

Guide

Reference