Different SQL TimeStamp functions in SQL Server

This article will show different SQL timestamp functions available with examples.

Microsoft owns the SQL Server, a popular and widely used relational database management system. SQL Server comes loaded with a bunch of different date and time functions. It can lead to confusion when you’re trying to find specifics regarding one in particular that you need for your current project.

SQL Server date and time data types

Understanding date and time data types in SQL Server is essential before I explain different timestamp functions.

Data Type	Format	Description
Date	YYYY-MM-DD	The Date data type can store values from 0001-01-01 through 9999-12-31. It uses 3 bytes of storage with an accuracy of 1 day.
Time	hh:mm:ss[.nnnnnnn]	The Time data type can store values from 00:00:00.0000000 through 23:59:59.9999999. It uses 3 to 5 bytes of storage. Its accuracy is 100 nanoseconds.
DateTime	YYYY-MM-DD hh:mm:ss[.nnn]	The DataTime function can store values from 1753-01-01 through 9999-12-31. Its accuracy is 0.00333 seconds. It uses 8 bytes of storage.
smalldatetime	YYYY-MM-DD hh:mm:ss	The SmallDateTime function can store values from 1900-01-01 through 2079-06-06. It uses 4 bytes of storage. Its accuracy is 1 minute.
DateTime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn];	The DateTime2 function stores values from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999. Its accuracy is 100 nanoseconds with 6-8 bytes of storage.
DateTimeOffset	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+\|-]hh:mm	The DateTimeOffset function stores values from 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC). Its accuracy is 100 nanoseconds and uses 8-10 bytes of storage.

SQL TimeStamp functions functions

The SQL Server TimeStamp functions can be divided into the following categories.

Functions to return system date and time values
Functions to return date and time parts
Functions to return date and time values from their parts
Functions to return date and time difference values
Functions to modify date and time values
Functions to validate date and time values

Let’s explore each category related to SQL Server TimeStamp functions.

SQL TimeStamp functions to return system date and time values

The SQL Server TimeStamp functions to return date and time values are classified as higher and lower precision functions.

Higher precision timestamp functions

SYSDATETIME()
- - The SYSDATETIME() function gives the date and time of the SQL Server machine.
  - It uses datetime2(7) data type output value.
  - Query: SELECT SYSDATETIME() AS ‘DateAndTime’; output 2022-06-26 15:51:18.6207415
SYSDATETIMEOFFSET()
- - The SYSDATETIMEOFFSET() gives the date and time of the SQL Server machine plus the offset from UTC.
  - It returns a DateTimeOffset(7) data type value .
  - Query: SELECT SYSDATETIMEOFFSET() AS ‘DateAndTime+Offset’; output 2022-06-26 15:51:18.6207415 +05:30
SYSUTCDATETIME()
- - The output of the SYSUTCDATETIME() function is the date and time of the SQL Server machine as UTC.
  - It returns a datetime2(7) data type value.
  - Query: SELECT SYSUTCDATETIME() AS ‘DateAndTimeInUtc’; output 2022-06-26 10:21:18.6207415

SELECT SYSDATETIME() AS 'DateAndTime',SYSDATETIMEOFFSET() AS 'DateAndTime+Offset',

SYSUTCDATETIME() AS 'DateAndTimeInUtc';

Lower precision timestamp function

CURRENT_TIMESTAMP:
- - It returns a DateTime value containing the date and time of the computer on which the instance of SQL Server runs.
  - Note: The function does not require any parentheses.
  - Query: SELECT CURRENT_TIMESTAMP AS ‘DateAndTime’; Output: 2022-06-26 15:59:05.660
GETDATE():
- - It returns a DateTime value containing the date and time of the computer on which the instance of SQL Server runs.
  - Query: SELECT GETDATE() AS ‘DateAndTime’; Output: 2022-06-26 16:00:28.350
GETUTCDATE():
- - It returns the DateTime value as UTC (Universal Coordinated Time).
  - Query: SELECT GETUTCDATE() AS ‘DateAndTime’; Output: 2022-06-26 10:33:57.047

SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP',GETDATE() AS 'GETDATE' ,

GETUTCDATE() AS 'GETUTCDATE';

Functions to return date and time parts

DATENAME()
- It returns a string representing the specified datepart of the specified date. The following table shows the parameter and its output in the DATENAME() function.

Datepart	Query	Output
year, yyyy, yy	SELECT DATENAME(YEAR, GETDATE()) AS ‘Year’;	2022
quarter, qq, q	SELECT DATENAME(QUARTER, GETDATE()) AS ‘Quarter’;	2
month, mm, m	SELECT DATENAME(MONTH, GETDATE()) AS ‘Month Name’;	June
dayofyear, dy, y	SELECT DATENAME(DAYOFYEAR, GETDATE()) AS ‘DayOfYear’;	177
day, dd, d	SELECT DATENAME(DAY, GETDATE()) AS ‘Day’;	26
week, wk, ww	SELECT DATENAME(WEEK, GETDATE()) AS ‘Week’;	27
weekday, dw	SELECT DATENAME(WEEKDAY, GETDATE()) AS ‘Day of the Week’;	Sunday
hour, hh	SELECT DATENAME(HOUR, GETDATE()) AS ‘Hour’;	16
minute, n	SELECT DATENAME(MINUTE, GETDATE()) AS ‘Minute’;	50
second, ss, s	SELECT DATENAME(SECOND, GETDATE()) AS ‘Second’;	1
millisecond, ms	SELECT DATENAME(MILLISECOND, GETDATE()) AS ‘MilliSecond’;	633
microsecond, mcs	SELECT DATENAME(MICROSECOND, GETDATE()) AS ‘MicroSecond’;	150000
nanosecond, ns	SELECT DATENAME(NANOSECOND, GETDATE()) AS ‘NanoSecond’;	756666666
ISO_WEEK, ISOWK, ISOWW	SELECT DATENAME(ISO_WEEK, GETDATE()) AS ‘Week’;	25

DATEPART()
- The DATEPART function output is an integer representing the specified datepart of the specified date.

Datepart
year, yyyy, yy	SELECT DATEPART(YEAR, GETDATE()) AS ‘Year’;	2022
quarter, qq, q	SELECT DATEPART(QUARTER, GETDATE()) AS ‘Quarter’;	2
month, mm, m	SELECT DATEPART(MONTH, GETDATE()) AS ‘Month’;	6
dayofyear, dy, y	SELECT DATEPART(DAYOFYEAR, GETDATE()) AS ‘DayOfYear’;	177
day, dd, d	SELECT DATEPART(DAY, GETDATE()) AS ‘Day’;	26
week, wk, ww	SELECT DATEPART(WEEK, GETDATE()) AS ‘Week’;	27
weekday, dw	SELECT DATEPART(WEEKDAY, GETDATE()) AS ‘Day of the Week’;	1
hour, hh	SELECT DATEPART(HOUR, GETDATE()) AS ‘Hour’;	17
minute, n	SELECT DATEPART(MINUTE, GETDATE()) AS ‘Minute’;	2
second, ss, s	SELECT DATEPART(SECOND, GETDATE()) AS ‘Second’;	41
millisecond, ms	SELECT DATEPART(MILLISECOND, GETDATE()) AS ‘MilliSecond’;	303
microsecond, mcs	SELECT DATEPART(MICROSECOND, GETDATE()) AS ‘MicroSecond’;;	140000
nanosecond, ns	SELECT DATEPART(NANOSECOND, GETDATE()) AS ‘NanoSecond’;	540000000
ISO_WEEK, ISOWK, ISOWW	SELECT DATEPART(ISO_WEEK, GETDATE()) AS ‘Week’;	25

SQL Server DAY, MONTH, and YEAR Function
- DAY()
  - It returns an integer corresponding to the day specified.
  - Query: SELECT DAY(GETDATE()) AS ‘Day’; Output 26
- MONTH()
  - It returns an integer corresponding to the month specified.
  - Query: SELECT MONTH(GETDATE()) AS ‘Month’; Output 6
- YEAR()
  - It returns an integer corresponding to the year specified
  - Query: SELECT YEAR(GETDATE()) AS ‘Year’; Output 2022

If you specify value 0 in the DAY(), MONTH(), and YEAR() function, it returns the output as January 1, 1900.

SELECT YEAR(0) as 'Year', MONTH(0) as 'Month', DAY(0) as 'Day';

Functions to return date and time values from their parts

DATEFROMPARTS
- It returns a date value from the specified year, month, and day.
- Syntax: DATEFROMPARTS ( year, month, day )
- Query: SELECT DATEFROMPARTS ( 2022, 06, 26 ) AS ‘Date’;

DATETIME2FROMPARTS
- It returns the DateTime2data type value for the specified date and time and specified precision.
- Syntax:
  - DATETIME2FROMPARTS ( yy, mm, day, hour, minute, seconds, fractions, precision )
- Query: SELECT DATETIME2FROMPARTS ( 2022, 06, 26, 16, 30, 15, 0, 0 ) AS Result;

A fractions value of 5 and a precision value of 1 represents 5/10 of a second.

Query: SELECT DATETIME2FROMPARTS ( 2022, 06, 26, 16, 30, 15, 5, 1 ) AS Result;

$DATETIME2FROMPARTS with precision and fraction$

- A fractions value of 50 and a precision value of 2 represents 50/100 of a second.

Query: SELECT DATETIME2FROMPARTS ( 2022, 06, 26, 16, 30, 15, 50, 2 ) AS Result;

$DATETIME2FROMPARTS with precision and fraction output$

A fractions value of 500 and a precision value of 3 represents 500/1000 of a second.

Query: SELECT DATETIME2FROMPARTS ( 2022, 06, 26, 16, 30, 15, 500, 3 ) AS Result;

DATETIMEFROMPARTS
- It returns a DateTime value for the specified date and time arguments.
- Syntax: DATETIMEFROMPARTS ( yy, mm, day, hour, minute, seconds, milliseconds )

Query: SELECT DATETIMEFROMPARTS ( 2022, 06, 26, 11, 01, 59, 0 ) AS Result;

DATETIMEOFFSETFROMPARTS
- It returns a datetimeoffset value for the specified date and time, with the specified offsets and precision.
- Syntax: DATETIMEOFFSETFROMPARTS ( yy, mm, dd, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

Query: SELECT DATETIMEOFFSETFROMPARTS ( 2022, 06, 26, 15, 14, 23, 0, 12, 0, 7 ) AS Result;

- If we use a fractions value of 5 and a precision value of 1, the value of fractions represents 5/10 of a second.

SELECT DATETIMEOFFSETFROMPARTS ( 2022, 06, 26, 15, 14, 23, 5, 12, 1, 7 ) AS Result;

If we use a fractions value of 50 and a precision value of 2, the value of fractions represents 50/100 of a second.

SELECT DATETIMEOFFSETFROMPARTS ( 2022, 06, 26, 15, 14, 23, 50, 12, 2, 7 ) AS Result;

- If we use a fraction value of 500 and a precision value of 3, the value of fractions represents 500/1000 of a second.

SELECT DATETIMEOFFSETFROMPARTS ( 2022, 06, 26, 15, 14, 23, 500, 12, 3, 7 ) AS Result;

SMALLDATETIMEFROMPARTS
- It returns a smalldatetime value for the specified date and time.
- Syntax: SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

Query: SELECT SMALLDATETIMEFROMPARTS ( 2022, 06, 26, 23, 59 ) AS Result

TIMEFROMPARTS
- It returns a time value for the specified time with the specified precision.
- Syntax: TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Query: SELECT TIMEFROMPARTS ( 22, 59, 59, 0, 0 ) AS Result;

If we use the fraction and precision value as per the logic specified earlier, the TIMEFROMPARTS function returns the following output:

SELECT TIMEFROMPARTS ( 22, 59, 59, 5, 1 ) as fp1,

TIMEFROMPARTS ( 22, 59, 59, 50, 2) as fp2,

TIMEFROMPARTS ( 22, 59, 59, 500, 3 ) as fp3

Functions to return date and time difference values

DATEDIFF
- It returns the number of date or time datepart boundaries crossed between two specified dates.
- Syntax: DATEDIFF ( datepart , startdate , enddate )

Query:

SELECT

DATEDIFF(year,'2021-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'Year',

DATEDIFF(quarter, '2021-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'quarter',

DATEDIFF(month, '2021-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'Month',

DATEDIFF(dayofyear, '2021-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'dayofyear',

DATEDIFF(day, '2021-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'day',

DATEDIFF(week, '2021-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'week',

DATEDIFF(hour, '2022-06-26 00:00:00.0000000', '2022-06-26 23:59:59.9999999') as 'hour',

DATEDIFF(minute, '2022-06-26 00:00:00.0000000', '2022-06-26 23:59:59.9999999') as 'minute',

DATEDIFF(second, '2022-06-26 00:00:00.0000000', '2022-06-26 23:59:59.9999999') 'Seconds'

DATEDIFF_BIG
- It returns the number of date or time datepart boundaries crossed between specified dates as a bigint
- Syntax: DATEDIFF_BIG (datepart , startdate , enddate)

SELECT

DATEDIFF_BIG(year,'2005-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'Year',

DATEDIFF_BIG(quarter, '2005-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'quarter',

DATEDIFF_BIG(month, '2005-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'Month',

DATEDIFF_BIG(dayofyear, '2005-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'dayofyear',

DATEDIFF_BIG(day, '2005-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'day',

DATEDIFF_BIG(week, '2005-12-31 23:59:59.9999999', '2022-06-26 00:00:00.0000000') as 'week',

DATEDIFF_BIG(hour, '2005-12-31 23:59:59.9999999', '2022-06-26 23:59:59.9999999') as 'hour',

DATEDIFF_BIG(minute, '2005-12-31 23:59:59.9999999', '2022-06-26 23:59:59.9999999') as 'minute',

DATEDIFF_BIG(second, '2005-12-31 23:59:59.9999999', '2022-06-26 23:59:59.9999999') 'Seconds'

Functions to modify date and time values

DATEADD
- It returns a new DATETIME value by adding an interval to the specified datepart of the specified date.
- Syntax: DATEADD (datepart, number, date )

DECLARE @datetime datetime = '2022-06-26 01:00:00';

SELECT 'year', DATEADD(year,1,@datetime)

UNION ALL

SELECT 'quarter',DATEADD(quarter,1,@datetime)

UNION ALL

SELECT 'month',DATEADD(month,1,@datetime)

UNION ALL

SELECT 'dayofyear',DATEADD(dayofyear,1,@datetime)

UNION ALL

SELECT 'day',DATEADD(day,1,@datetime)

UNION ALL

SELECT 'week',DATEADD(week,1,@datetime)

UNION ALL

SELECT 'weekday',DATEADD(weekday,1,@datetime)

UNION ALL

SELECT 'hour',DATEADD(hour,1,@datetime)

UNION ALL

SELECT 'minute',DATEADD(minute,1,@datetime)

UNION ALL

SELECT 'second',DATEADD(second,1,@datetime)

UNION ALL

SELECT 'millisecond',DATEADD(millisecond,1,@datetime)

EOMONTH
- It returns the last day of the month containing the specified date.
- Syntax: EOMONTH ( start_date [, month_to_add ] )

DECLARE @date DATETIME = GETDATE();

SELECT

EOMONTH ( @date ) AS 'This Month',

EOMONTH ( @date, 1 ) AS 'Next Month',

EOMONTH ( @date, -1 ) AS 'Last Month';

SWITCHOFFSET
- The SWITCHOFFSET function changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value.
- Syntax: SWITCHOFFSET ( datetimeoffset_expression, timezoneoffset_expression )

SELECT SWITCHOFFSET(GETDATE(), -5) AS 'Result';

TODATETIMEOFFSET
- TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. TODATETIMEOFFSET interprets the datetime2 value in local time for the specified time_zone.
- Syntax: TODATETIMEOFFSET ( datetime_expression , timezoneoffset_expression )

SELECT TODATETIMEOFFSET(SYSDATETIME(), -120) 'Result1',

TODATETIMEOFFSET(SYSDATETIME(), '+13:00') as 'Result2'

SQL TimeStamp functions to validate date and time values

ISDATE
- The ISDATE() function determines whether a DateTime or smalldatetime input expression has a valid date or time value.

IF ISDATE('2022-05-12 10:19:41.177') = 1

SELECT 'VALID';

ELSE

SELECT 'INVALID';

IF ISDATE('2022-05-32 10:19:41.177') = 1

SELECT 'VALID';

ELSE

SELECT 'INVALID';

Conclusion

This article explored the various SQL TimeStamp functions along with examples. You can use these functions to work with date time values in SQL Server and extract a relevant part from the timestamp.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023

SQL Server date and time data types

SQL TimeStamp functions functions

SQL TimeStamp functions to return system date and time values

Higher precision timestamp functions

Lower precision timestamp function

Functions to return date and time parts

Functions to return date and time values from their parts

Functions to return date and time difference values

Functions to modify date and time values

SQL TimeStamp functions to validate date and time values

Conclusion

Related posts: