SQL Server Date and Time Functions with Examples

By:   |   Updated: 2021-05-17   |   Comments (2)   |   Related: More > Dates


Please do not scroll away - stay informed.
Dear Database Professional,

Did you know that MSSQLTips.com publishes new SQL Server content on a daily basis as well as offers free webinars and tutorials?

Let us help you stay informed and learn something new each day. Click here to keep informed.

Thank you,
Greg Robidoux and Jeremy Kadlec (MSSQLTips.com Co-Founders)
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.

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.

SQL Server 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)
 
SQL Server T-SQL SyntaxDate FunctionResult
SELECT SYSDATETIME() AS 'DateAndTime'; -- return datetime2(7)DateAndTime2019-03-08 10:24:34.4377944
SELECT SYSDATETIMEOFFSET() AS 'DateAndTime+Offset'; -- datetimeoffset(7)DateAndTime+Offset2019-03-08 10:24:34.4377944 -05:00
SELECT SYSUTCDATETIME() AS 'DateAndTimeInUtc'; -- returns datetime2(7)DateAndTimeInUtc2019-03-08 15:24:34.4377944

SQL Server 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'; 
 
SQL Server T-SQL SyntaxDate FunctionResult
SELECT CURRENT_TIMESTAMP AS 'DateAndTime'; -- note: no parenthesesDateAndTime2019-03-08 10:28:23.643
SELECT GETDATE() AS 'DateAndTime';DateAndTime2019-03-08 10:28:23.643
SELECT GETUTCDATE() AS 'DateAndTimeUtc';DateAndTimeUtc2019-03-08 15:28:23.643

SQL Server 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'; 
 
SQL Server T-SQL SyntaxDate FunctionResult
SELECT DATENAME(YEAR, GETDATE()) AS 'Year';Year2019
SELECT DATENAME(QUARTER, GETDATE()) AS 'Quarter';Quarter1
SELECT DATENAME(MONTH, GETDATE()) AS 'Month';MonthMarch
SELECT DATENAME(DAYOFYEAR, GETDATE()) AS 'DayOfYear';DayOfYear67
SELECT DATENAME(DAY, GETDATE()) AS 'Day';Day8
SELECT DATENAME(WEEK, GETDATE()) AS 'Week';Week10
SELECT DATENAME(WEEKDAY, GETDATE()) AS 'WeekDay';WeekDayFriday
SELECT DATENAME(HOUR, GETDATE()) AS 'Hour';Hour11
SELECT DATENAME(MINUTE, GETDATE()) AS 'Minute';Minute25
SELECT DATENAME(SECOND, GETDATE()) AS 'Second';Second44
SELECT DATENAME(MILLISECOND, GETDATE()) AS 'MilliSecond';MilliSecond426
SELECT DATENAME(MICROSECOND, GETDATE()) AS 'MicroSecond';MicroSecond426666
SELECT DATENAME(NANOSECOND, GETDATE()) AS 'NanoSecond';NanoSecond426666666
SELECT DATENAME(ISO_WEEK, GETDATE()) AS 'Week';Week10

SQL Server 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'; 
 
SQL Server T-SQL SyntaxDate FunctionResult
SELECT DATEPART(YEAR, GETDATE()) AS 'Year';Year2019
SELECT DATEPART(QUARTER, GETDATE()) AS 'Quarter';Quarter1
SELECT DATEPART(MONTH, GETDATE()) AS 'Month';Month3
SELECT DATEPART(DAYOFYEAR, GETDATE()) AS 'DayOfYear';DayOfYear67
SELECT DATEPART(DAY, GETDATE()) AS 'Day';Day8
SELECT DATEPART(WEEK, GETDATE()) AS 'Week';Week10
SELECT DATEPART(WEEKDAY, GETDATE()) AS 'WeekDay';WeekDay6
SELECT DATEPART(HOUR, GETDATE()) AS 'Hour';Hour10
SELECT DATEPART(MINUTE, GETDATE()) AS 'Minute';Minute36
SELECT DATEPART(SECOND, GETDATE()) AS 'Second';Second14
SELECT DATEPART(MILLISECOND, GETDATE()) AS 'MilliSecond';MilliSecond43
SELECT DATEPART(MICROSECOND, GETDATE()) AS 'MicroSecond';MicroSecond43333
SELECT DATEPART(NANOSECOND, GETDATE()) AS 'NanoSecond';NanoSecond43333333
SELECT DATEPART(ISO_WEEK, GETDATE()) AS 'Week';Week10

SQL Server 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'; 
 
SQL Server T-SQL SyntaxDate FunctionResult
SELECT DAY(GETDATE()) AS 'Day';DAY8
SELECT MONTH(GETDATE()) AS 'Month';MONTH3
SELECT YEAR(GETDATE()) AS 'Year';YEAR2019

SQL Server 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
 
SQL Server T-SQL SyntaxDate FunctionResult
SELECT DATEFROMPARTS(2019,1,1) AS 'Date';Date2019-01-01
SELECT DATETIME2FROMPARTS(2019,1,1,6,0,0,0,1) AS 'DateTime2';DateTime22019-01-01 06:00:00.0
SELECT DATETIMEFROMPARTS(2019,1,1,6,0,0,0) AS 'DateTime';DateTime2019-01-01 06:00:00.000
SELECT DATETIMEOFFSETFROMPARTS(2019,1,1,6,0,0,0,0,0,0) AS 'Offset';Offset2019-01-01 06:00:00 +00:00
SELECT SMALLDATETIMEFROMPARTS(2019,1,1,6,0) AS 'SmallDateTime';SmallDateTime2019-01-01 06:00:00
SELECT TIMEFROMPARTS(6,0,0,0,0) AS 'Time';Time06:00:00

SQL Server 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
 
SQL Server T-SQL SyntaxDate FunctionResult
SELECT DATEDIFF(DAY, 2019-31-01, 2019-01-01) AS 'DateDif'DateDif30
SELECT DATEDIFF_BIG(DAY, 2019-31-01, 2019-01-01) AS 'DateDifBig'DateDifBig30

SQL Server 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
 
SQL Server T-SQL SyntaxDate FunctionResult
SELECT DATEADD(DAY,1,GETDATE()) AS 'DatePlus1';DatePlus12019-03-09 10:38:21.710
SELECT EOMONTH(GETDATE(),1) AS 'LastDayOfNextMonth';LastDayOfNextMonth2019-04-30
SELECT SWITCHOFFSET(GETDATE(), -6) AS 'NowMinus6';NowMinus62019-03-08 12:40:22.540 -00:06
SELECT TODATETIMEOFFSET(GETDATE(), -2) AS 'Offset';Offset2019-03-08 12:46:22.540 -00:02

SQL Server ISDATE Function to Validate Date and Time Values

  • 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';
 
SQL Server T-SQL SyntaxDate FunctionResult
SELECT ISDATE(GETDATE()) AS 'IsDate';IsDate1
SELECT ISDATE(NULL) AS 'IsDate';IsDate0
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:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights








About the author
Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2021-05-17

Comments For This Article




Monday, April 1, 2019 - 3:26:30 AM - LubabaloBack To Top(79435)

Good day

Can someone please advise, I have a column called [HOUR] in time(7) data type looking like '12:30:00.0000000', what sql function can I use to format the whole column without changing the data type, I tried a few but I'm not winning.

From hh:mm:ss[.nnnnnnn] to hh:mm

Regards

Lubabalo


Tuesday, March 26, 2019 - 10:40:14 AM - SharBack To Top(79399)

 Very helpful! Some of these I did not know about, ie the UTC functions. Thanks for putting this together.