SQL Server Date and Time Functions with Examples

Problem

SQL Server has several different date and time functions and trying to remember every function is not that easy.  So, I put together this tutorial that shows the different date and time functions all in one place along with examples to make finding what you are looking for much easier to find SQL date functions in SQL Server.

Solution

I think you’ll find this tutorial handy. It was put together as a quick reference to common SQL Server date and time functions. It’s broken in the same sections as the Microsoft documentation:

• Higher Precision functions
• Lesser Precision functions
• Date and Time Parts functions
• Date and Time from Parts functions
• Date and Time Difference Values functions
• Modify Date and Time Values functions
• Validate Date and Time Values functions

The SQL was tested on SQL Server 2016 and GETDATE() is used wherever possible as I thought it made things simpler. Note: most of these functions will work for versions earlier than SQL 2016, but some may not.

SYSDATETIME, SYSDATETIMEOFFSET and SYSUTCDATETIME Functions

SQL Server High Precision Date and Time Functions have a scale of 7 and are:

• SYSDATETIME – returns the date and time of the machine the SQL Server is running on
• SYSDATETIMEOFFSET – returns the date and time of the machine the SQL Server is running on plus the offset from UTC
• SYSUTCDATETIME – returns the date and time of the machine the SQL Server is running on as UTC

-- higher precision functions 
SELECT SYSDATETIME()       AS 'DateAndTime';        -- return datetime2(7)       
SELECT SYSDATETIMEOFFSET() AS 'DateAndTime+Offset'; -- datetimeoffset(7)
SELECT SYSUTCDATETIME()    AS 'DateAndTimeInUtc';   -- returns datetime2(7)

CURRENT_TIMESTAMP, GETDATE() and GETUTCDATE() Functions

SQL Server Lesser Precision Data and Time Functions have a scale of 3 and are:

• CURRENT_TIMESTAMP – returns the date and time of the machine the SQL Server is running on
• GETDATE() – returns the date and time of the machine the SQL Server is running on
• GETUTCDATE() – returns the date and time of the machine the SQL Server is running on as UTC

-- lesser precision functions - returns datetime
SELECT CURRENT_TIMESTAMP AS 'DateAndTime'; -- note: no parentheses   
SELECT GETDATE()         AS 'DateAndTime';    
SELECT GETUTCDATE()      AS 'DateAndTimeUtc'; 

DATENAME Function

• DATENAME – Returns a string corresponding to the datepart specified for the given date as shown in the following table

-- date and time parts - returns nvarchar 
SELECT DATENAME(YEAR, GETDATE())        AS 'Year';        
SELECT DATENAME(QUARTER, GETDATE())     AS 'Quarter';     
SELECT DATENAME(MONTH, GETDATE())       AS 'Month Name';       
SELECT DATENAME(DAYOFYEAR, GETDATE())   AS 'DayOfYear';   
SELECT DATENAME(DAY, GETDATE())         AS 'Day';         
SELECT DATENAME(WEEK, GETDATE())        AS 'Week';        
SELECT DATENAME(WEEKDAY, GETDATE())     AS 'Day of the Week';     
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

• DATEPART – returns an integer corresponding to the datepart specified

-- 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

• 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

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

DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS and  TIMEFROMPARTS Functions

• DATEFROMPARTS – 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

DATEDIFF and DATEDIFF_BIG Functions

• DATEDIFF – returns the number of date or time datepart boundaries crossed between specified dates as an int
• DATEDIFF_BIG – returns the number of date or time datepart boundaries crossed between specified dates as a bigint

--Date and Time Difference
SELECT DATEDIFF(DAY, 2019-31-01, 2019-01-01)      AS 'DateDif'    -- returns int
SELECT DATEDIFF_BIG(DAY, 2019-31-01, 2019-01-01)  AS 'DateDifBig' -- returns bigint

DATEADD, EOMONTH, SWITCHOFFSET and TODATETIMEOFFSET Functions

• 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

ISDATE Function to Validate Date and Time Values

• The ISDATE – returns int – Returns 1 if a valid datetime type and 0 if not

-- validate date and time - returns int
SELECT ISDATE(GETDATE()) AS 'IsDate'; 
SELECT ISDATE(NULL) AS 'IsDate';

Next Steps

Hopefully you found this tip helpful. 

As this was written to be a quick reference, the following links have more information regarding datetime functions and formatting:

• Tips on MSSQLTips
  • Date and Time Conversions Using SQL Server
  • SQL Server Dates Tips
  • SQL Server Date Time Calculation Examples
  • New Date and Time Functions in SQL Server
  • SQL Servers Lag and Lead Functions to Help Identify Date Differences
  • Restore SQL Server Databases using DateTime functions
  • SQL Server DateTime Best Practices
  • Format SQL Server Dates with FORMAT Function
• Other Resources
  • https://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar
  • Search – Date and Time Functions
  • https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-datatype
  • https://www.red-gate.com/simple-talk/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/
  • https://dba.stackexchange.com/questions/210683/what-is-the-current-date-or-current-date-value-function-for-sql-server
  • https://stackoverflow.com/questions/1114307/extracting-hours-from-a-datetime-sql-server-2005
  • https://www.red-gate.com/simple-talk/sql/t-sql-programming/how-to-get-sql-server-dates-and-times-horribly-wrong/
  • https://blog.sqlauthority.com/2009/08/06/sql-server-get-time-in-hourminute-format-from-a-datetime-get-date-part-only-from-datetime/

Joe Gavin

Joe Gavin is from Greater Boston and has worked in technology as a Field Service Engineer for an automotive dealer computer system vendor, a Technical Consultant and Operations Analyst with Sybase and SQL Server based database applications in financial services and now works as a SQL Server Database Administrator.

• MSSQLTips Awards:
• Achiever Award (75+ Tips) – 2024 | Author of the Year – 2021 | Author Contender – 2024 |
• Rookie Contender – 2018