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|