T-SQL Date and Time Functions

This section provides a comprehensive reference to the Transact-SQL (T-SQL) functions available for working with date and time data types in SQL Server.

Date and Time Functions

GETDATE

GETDATE ( )

Returns the current database system timestamp as a datetime2(3) value.

GETDATE() is a non-deterministic function. The value returned by the function can be different each time it is executed.

Example:

SELECT GETDATE();

SYSDATETIME

SYSDATETIME ( )

Returns the date and time of the server on which the instance of SQL Server is running.

SYSDATETIME() is a non-deterministic function. The value returned by the function can be different each time it is executed.

Example:

SELECT SYSDATETIME();

DATEADD

DATEADD ( datepart, number, date )

Returns a specified date with a specified time interval added to it.

Parameters:
datepart: The part of the date to which the number is added. For a list of valid datepart values, see Datepart Values.
number: The integer expression that represents the number of units to add.
date: The date expression on which you are operating.

Example:

SELECT DATEADD(month, 6, '2023-08-15');

DATEDIFF

DATEDIFF ( datepart, startdate, enddate )

Returns the number of date and time parts between two specified dates.

Parameters:
datepart: The part of the date for which the difference is returned. For a list of valid datepart values, see Datepart Values.
startdate: The first date expression.
enddate: The second date expression.

Example:

SELECT DATEDIFF(day, '2023-01-01', '2023-03-15');

DATEPART

DATEPART ( datepart, date )

Returns an integer representing the specified date part of the specified date.

Parameters:
datepart: The part of the date to return. For a list of valid datepart values, see Datepart Values.
date: The date expression on which you are operating.

Example:

SELECT DATEPART(month, '2023-10-26');

DATENAME

DATENAME ( datepart, date )

Returns a character string representing the specified date part of the specified date.

Parameters:
datepart: The part of the date to return. For a list of valid datepart values, see Datepart Values.
date: The date expression on which you are operating.

Example:

SELECT DATENAME(weekday, '2023-10-26');

DAY

DAY ( date )

Returns the day of the month for a specified date.

Parameters:
date: The date expression.

Example:

SELECT DAY('2023-10-26');

MONTH

MONTH ( date )

Returns an integer that represents the month of a specified date.

Parameters:
date: The date expression.

Example:

SELECT MONTH('2023-10-26');

YEAR

YEAR ( date )

Returns an integer that represents the year of a specified date.

Parameters:
date: The date expression.

Example:

SELECT YEAR('2023-10-26');

FORMAT

FORMAT ( value, format [, culture ] )

Formats a value and converts it to text in a specified format.

Parameters:
value: The value to format. Can be any expression that returns a date, time, or number.
format: The format string. Can be a standard format string or a custom format string.
culture: Optional. The culture to use for formatting. If not specified, the current session culture is used.

Example:

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss');

ISDATE

ISDATE ( string )

Determines whether the input expression is a valid date or time.

Parameters:
string: The expression to test.

ISDATE returns 1 if the string is a valid date or time, and 0 otherwise.

Example:

SELECT ISDATE('2023-10-26');

SWITCHOFFSET

SWITCHOFFSET ( datetimeoffset_expression, timezoneoffset )

Changes the offset of a datetimeoffset value to a specified offset.

Parameters:
datetimeoffset_expression: An expression of the datetimeoffset data type.
timezoneoffset: The new time zone offset to apply to the datetimeoffset value. The offset is specified in the format +HH:MM or -HH:MM.

Example:

SELECT SWITCHOFFSET('2023-10-26 10:00:00 +02:00', '+05:30');

TODATETIME2

TODATETIME2 ( input [, date_style ] )

Converts various date and time data types to datetime2.

Parameters:
input: An expression of any valid date and time data type.
date_style: Optional. An integer that specifies the style of the input date.

Example:

SELECT TODATETIME2('2023-10-26');

TODATETIMEOFFSET

TODATETIMEOFFSET ( expression [, timezoneoffset ] )

Converts various date and time data types to datetimeoffset.

Parameters:
expression: An expression of any valid date and time data type.
timezoneoffset: Optional. The time zone offset to apply to the converted value.

Example:

SELECT TODATETIMEOFFSET('2023-10-26 10:00:00', '+03:00');

Common Datepart Values: