Date and Time Functions (Transact-SQL)

This section details the Transact-SQL (T-SQL) functions available for working with date and time data types in Microsoft SQL Server.

Overview

SQL Server provides a rich set of built-in functions to manipulate and query date and time values. These functions are essential for tasks such as calculating durations, formatting dates, extracting specific components, and performing date-based comparisons.

Categories of Date and Time Functions

Retrieving Current Date and Time

• GETDATE()
• SYSDATETIME()
• CURRENT_TIMESTAMP
• GETUTCDATE()

Date and Time Conversion and Formatting

• CONVERT()
• CAST()
• FORMAT()

Date and Time Manipulation

• DATEADD()
• DATEDIFF()
• DATENAME()
• DATEPART()
• DAY()
• MONTH()
• YEAR()
• EOMONTH()

Date and Time Comparison

• ISDATE()

Detailed Function Descriptions

GETDATE()

Returns the current database system timestamp as a datetime value. This value is set at the beginning of the statement execution.

SELECT GETDATE();

SYSDATETIME()

Returns the SQL Server system timestamp as a datetime2(7) value. This value is based on the operating system of the computer that is running SQL Server.

SELECT SYSDATETIME();

CURRENT_TIMESTAMP

Returns the current date and time as a datetime value. It is the ANSI SQL equivalent of the @@DATEFIRST setting and GETDATE.

SELECT CURRENT_TIMESTAMP;

GETUTCDATE()

Returns the current UTC (Coordinated Universal Time) date and time as a datetime value.

SELECT GETUTCDATE();

CONVERT()

Converts an expression to a specified data type. It is commonly used to format dates.

SELECT CONVERT(VARCHAR, GETDATE(), 103); -- DD/MM/YYYY format

CAST()

Converts an expression from one data type to another. Similar to CONVERT but with a different syntax.

SELECT CAST(GETDATE() AS DATE); -- Truncates time part

FORMAT()

Formats a value with the specified format and optional culture. Introduced in SQL Server 2012.

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

DATEADD()

Returns a new datetime value by adding a specified time interval to a specified date.

SELECT DATEADD(day, 5, '2023-10-27'); -- Adds 5 days

DATEDIFF()

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

SELECT DATEDIFF(hour, '2023-10-27 10:00:00', '2023-10-27 14:30:00'); -- Returns 4

DATENAME()

Returns a character string representing the specified datepart (e.g., 'year', 'month', 'day') of the specified date.

SELECT DATENAME(weekday, GETDATE()); -- Returns the name of the day of the week

DATEPART()

Returns an integer representing the specified datepart (e.g., 'year', 'month', 'day') of the specified date.

SELECT DATEPART(month, GETDATE()); -- Returns the month number

DAY()

Returns the day of the month for the specified date as an integer.

SELECT DAY('2023-10-27'); -- Returns 27

MONTH()

Returns the month of the specified date as an integer.

SELECT MONTH('2023-10-27'); -- Returns 10

YEAR()

Returns the year of the specified date as an integer.

SELECT YEAR('2023-10-27'); -- Returns 2023

EOMONTH()

Returns the last day of the month containing the specified date, with an optional offset indicating the number of months before or after the month of the specified date.

SELECT EOMONTH('2023-10-15', 1); -- Returns the last day of November 2023

ISDATE()

Tests if the input expression is a valid date.

SELECT ISDATE('2023-10-27'); -- Returns 1 (True)
SELECT ISDATE('invalid-date'); -- Returns 0 (False)