Rajendra Gupta
EOMONTH function output

Different SQL TimeStamp functions in SQL Server

November 25, 2022 by

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

Different SQL TimeStamp functions in SQL Server

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

CURRENT_TIMESTAMP function

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.

YEAR, MONTH and DAY function

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

DATEFROMPARTS function output

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

DATETIME2FROMPARTS function output

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

DATETIME2FROMPARTS with precision

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

DATETIMEFROMPARTS SQL TimeStamp function

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

DATETIMEOFFSETFROMPARTS function output

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

Fraction and Precision input

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

query output

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

query with precision output

  • 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

SMALLDATETIMEFROMPARTS function

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

TIMEFROMPARTS SQL TimeStamp function

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

TIMEFORMATS function

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:

SQL DATEDIFF function

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

SQL DATEDIFF_BIG function

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 )

DATEADD function with different values

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

EOMONTH function output

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

SWITCHOFFSET  function

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

TODATETIMEOFFSET function and query

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.

ISDATE() function

ISDATE() function for valid and invalid dates

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.


Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views