Date and Time Functions in SQL Server T-SQL

Functions That Return Date and Time Parts

The following functions return date parts:

DATENAME – returns a string corresponding to the datepart specified

DATEPART – returns an integer corresponding to the datepart specified

DAY – returns an integer corresponding to the day specified

MONTH– returns an integer corresponding to the month specified

YEAR– returns an integer corresponding to the year specified

DATENAME Function

-- date and time parts - returns nvarchar
SELECT DATENAME(YEAR, GETDATE())        AS 'Year';
SELECT DATENAME(QUARTER, GETDATE())     AS 'Quarter';
SELECT DATENAME(MONTH, GETDATE())       AS 'Month';
SELECT DATENAME(DAYOFYEAR, GETDATE())   AS 'DayOfYear';
SELECT DATENAME(DAY, GETDATE())         AS 'Day';
SELECT DATENAME(WEEK, GETDATE())        AS 'Week';
SELECT DATENAME(WEEKDAY, GETDATE())     AS 'WeekDay';
SELECT DATENAME(HOUR, GETDATE())        AS 'Hour';
SELECT DATENAME(MINUTE, GETDATE())      AS 'Minute';
SELECT DATENAME(SECOND, GETDATE())      AS 'Second';
SELECT DATENAME(MILLISECOND, GETDATE()) AS 'MilliSecond';
SELECT DATENAME(MICROSECOND, GETDATE()) AS 'MicroSecond';
SELECT DATENAME(NANOSECOND, GETDATE())  AS 'NanoSecond';
SELECT DATENAME(ISO_WEEK, GETDATE())    AS 'Week';

DATEPART Function

-- date and time parts - returns int
SELECT DATEPART(YEAR, GETDATE())        AS 'Year'; 
SELECT DATEPART(QUARTER, GETDATE())     AS 'Quarter'; 
SELECT DATEPART(MONTH, GETDATE())       AS 'Month';
SELECT DATEPART(DAYOFYEAR, GETDATE())   AS 'DayOfYear';
SELECT DATEPART(DAY, GETDATE())         AS 'Day';
SELECT DATEPART(WEEK, GETDATE())        AS 'Week';
SELECT DATEPART(WEEKDAY, GETDATE())     AS 'WeekDay';
SELECT DATEPART(HOUR, GETDATE())        AS 'Hour';
SELECT DATEPART(MINUTE, GETDATE())      AS 'Minute';
SELECT DATEPART(SECOND, GETDATE())      AS 'Second';
SELECT DATEPART(MILLISECOND, GETDATE()) AS 'MilliSecond';
SELECT DATEPART(MICROSECOND, GETDATE()) AS 'MicroSecond';
SELECT DATEPART(NANOSECOND, GETDATE())  AS 'NanoSecond';
SELECT DATEPART(ISO_WEEK, GETDATE())    AS 'Week';

DAY, MONTH and YEAR Functions

SELECT DAY(GETDATE())   AS 'Day';
SELECT MONTH(GETDATE()) AS 'Month'; 
SELECT YEAR(GETDATE())  AS 'Year';

Functions That Return Date and Time Values from Their Parts

ATEFROMPARTS – returns a date from the date specified

DATETIME2FROMPARTS – returns a datetime2 from part specified

DATETIMEFROMPARTS – returns a datetime from part specified

DATETIMEOFFSETFROMPARTS - returns a datetimeoffset from part specified

SMALLDATETIMEFROMPARTS - returns a smalldatetime from part specified

TIMEFROMPARTS - returns a time from part specified

-- date and time from parts
SELECT DATEFROMPARTS(2019,1,1)                         AS 'Date';          -- returns date
SELECT DATETIME2FROMPARTS(2019,1,1,6,0,0,0,1)          AS 'DateTime2';     -- returns datetime2
SELECT DATETIMEFROMPARTS(2019,1,1,6,0,0,0)             AS 'DateTime';      -- returns datetime
SELECT DATETIMEOFFSETFROMPARTS(2019,1,1,6,0,0,0,0,0,0) AS 'Offset';        -- returns datetimeoffset
SELECT SMALLDATETIMEFROMPARTS(2019,1,1,6,0)            AS 'SmallDateTime'; -- returns smalldatetime
SELECT TIMEFROMPARTS(6,0,0,0,0)                        AS 'Time';          -- returns time

Functions That Modify Date and Time Values

DATEADD - returns datepart with added interval as a datetime

EOMONTH – returns last day of month of offset as type of start_date

SWITCHOFFSET - returns date and time offset and time zone offset

TODATETIMEOFFSET - returns date and time with time zone offset

-- modify date and time
SELECT DATEADD(DAY,1,GETDATE())        AS 'DatePlus1';          -- returns data type of the date argument
SELECT EOMONTH(GETDATE(),1)            AS 'LastDayOfNextMonth'; -- returns start_date argument or date
SELECT SWITCHOFFSET(GETDATE(), -6)     AS 'NowMinus6';          -- returns datetimeoffset
SELECT TODATETIMEOFFSET(GETDATE(), -2) AS 'Offset';             -- returns datetimeoffset