Microsoft SQL Server Dates DATEPART & DATENAME
Example
DATEPART returns the specified datepart of the specified datetime expression as a numeric value.
DATENAME returns a character string that represents the specified datepart of the specified date. In practice DATENAME is mostly useful for getting the name of the month or the day of the week.
There are also some shorthand functions to get the year, month or day of a datetime expression, which behave like DATEPART with their respective datepart units.
Syntax:
DATEPART ( datepart , datetime_expr )
DATENAME ( datepart , datetime_expr )
DAY ( datetime_expr )
MONTH ( datetime_expr )
YEAR ( datetime_expr )
Examples:
DECLARE @now DATETIME2 = GETDATE();
SELECT @now --2016-07-21 15:05:33.8370000
SELECT DATEPART(YEAR, @now) --2016
SELECT DATEPART(QUARTER, @now) --3
SELECT DATEPART(WEEK, @now) --30
SELECT DATEPART(HOUR, @now) --15
SELECT DATEPART(MINUTE, @now) --5
SELECT DATEPART(SECOND, @now) --33
-- Differences between DATEPART and DATENAME:
SELECT DATEPART(MONTH, @now) --7
SELECT DATENAME(MONTH, @now) --July
SELECT DATEPART(WEEKDAY, @now) --5
SELECT DATENAME(WEEKDAY, @now) --Thursday
--shorthand functions
SELECT DAY(@now) --21
SELECT MONTH(@now) --7
SELECT YEAR(@now) --2016
NOTE: DATEPART and DATENAME also accept abbreviations in the datepart parameter. Use of these abbreviations is generally discouraged as they can be confusing (m vs mi, ww vs w, etc.).
