DATEPART function returns an integer that represents the specified datepart of the specified date. This is very similar to DATENAME. This function returns an integer value. The return value depends on the language environment set by using "SET LANGUAGE". DATEPART function can be used in the SELECT list, WHERE, GROUP BY, HAVING, ORDER BY.
Syntax
The table below lists all valid datepart arguments
Examples
Syntax
DATEPART ( datepart, date )
datepart | Specifies the part of the date that DATEPART will return |
date | an expression that returns a date or datetime or datetimeoffset or datetime2 or smalldatetime or time or string in a valid date format value |
datepart | Abbreviations | Return Value |
---|---|---|
year | yy, yyyy | 1840-9999 |
quarter | qq, q | 1-4 |
month | mm, m | 1-12 |
dayofyear | dy, y | 1-366 |
day | dd, d | 1-31 |
week | wk, ww | 1-53 |
weekday | dw, w | 1-7 |
hour | hh | 0-23 |
minute | mi, n | 0-59 |
second | ss, s | 0-59 |
millisecond | ms | 0-99(with precision of 2) |
microsecond | mcs | |
nanosecond | ns | |
TZoffset | tz | |
ISO_WEEK | ISOWK, ISOWW | 1-53 |
DECLARE @date DATETIME
SET @date= '2019-08-27 17:26:27.640'
SELECT @date AS CurrentDate;------------------------------2013-04-07 23:28:42.013
SELECT DATEPART (year,@date) AS 'Year';--------------------2019
SELECT DATEPART (yy,@date) AS 'Year';----------------------2019
SELECT DATEPART (yyyy,@date) AS 'Year';--------------------2019
SELECT DATEPART (quarter,@date) AS 'Quarter';--------------3
SELECT DATEPART (qq,@date) AS 'Quarter';-------------------3
SELECT DATEPART (q,@date) AS 'Quarter';--------------------3
SELECT DATEPART (month,@date) AS 'Month';------------------8
SELECT DATEPART (mm,@date) AS 'Month';---------------------8
SELECT DATEPART (m,@date) AS 'Month';----------------------8
SELECT DATEPART (dayofyear,@date) AS 'Dayofyear';----------239
SELECT DATEPART (dy,@date) AS 'Dayofyear';-----------------239
SELECT DATEPART (y,@date) AS 'Dayofyear';------------------239
SELECT DATEPART (day,@date) AS 'Day';----------------------27
SELECT DATEPART (dd,@date) AS 'Day';-----------------------27
SELECT DATEPART (d,@date) AS 'Day';------------------------27
SELECT DATEPART (week,@date) AS 'Week';--------------------35
SELECT DATEPART (wk,@date) AS 'Week';----------------------35
SELECT DATEPART (ww,@date) AS 'Week';----------------------35
SELECT DATEPART (weekday,@date) AS 'Weekday';--------------3
SELECT DATEPART (dw,@date) AS 'Weekday';-------------------3
SELECT DATEPART (w,@date) AS 'Weekday';--------------------3
SELECT DATEPART (hour,@date) AS 'Hour';--------------------17
SELECT DATEPART (hh,@date) AS 'Hour';----------------------17
SELECT DATEPART (minute,@date) AS 'Minute';----------------26
SELECT DATEPART (mi,@date) AS 'Minute';--------------------26
SELECT DATEPART (n,@date) AS 'Minute';---------------------26
SELECT DATEPART (second,@date) AS 'Second';----------------27
SELECT DATEPART (ss,@date) AS 'Second';--------------------27
SELECT DATEPART (s,@date) AS 'Second';---------------------27
SELECT DATEPART (millisecond,@date) AS 'Milli Second';-----640
SELECT DATEPART (ms,@date) AS 'Milli Second';--------------640
SELECT DATEPART (microsecond,@date) AS 'Micro Second';-----640000
SELECT DATEPART (mcs,@date) AS 'Micro Second';-------------640000
SELECT DATEPART (nanosecond,@date) AS 'Nano Second';-------640000000
SELECT DATEPART (ns,@date) AS 'Nano Second';---------------640000000
SELECT DATEPART (TZoffset,SYSDATETIMEOFFSET()) AS 'TZoffset';-- 330 ( Returns minutes 330 -> +05:30 )
SELECT DATEPART (tz,SYSDATETIMEOFFSET()) AS 'TZoffset';-------- 330
SELECT DATEPART (ISO_WEEK,@date) AS 'ISO_WEEK';-------------35
SELECT DATEPART (ISOWK,@date) AS 'ISO_WEEK';----------------35
SELECT DATEPART (ISOWW,@date) AS 'ISO_WEEK';----------------35
0 comments:
Post a Comment