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|
RSQL, a simple alternative to Microsoft SQL Server