SQL Date Functions

This section provides detailed information about built-in date and time functions available in SQL Server. These functions allow you to manipulate, extract, and compare date and time values, which are crucial for many database operations.

Overview

Date and time data types in SQL Server are essential for tracking events, scheduling tasks, and analyzing temporal data. The following functions simplify common date-related operations.

Core Date Functions

GETDATE()

Returns the current database system timestamp as a datetime2(3) value. This value is set at the beginning of the statement execution and represents the time the statement started.

SELECT GETDATE();

DATEPART()

Returns a specified part of a date (e.g., day, month, year) as an integer.

Syntax: DATEPART ( datepart , date )

SELECT DATEPART(month, '2023-10-26'); -- Returns 10
SELECT DATEPART(weekday, GETDATE()); -- Returns the day of the week (e.g., 5 for Thursday if Sunday is 1)

DATENAME()

Returns a character string representing the specified date part of the specified date. The return value depends on the current language setting of the server.

Syntax: DATENAME ( datepart , date )

SELECT DATENAME(month, '2023-10-26'); -- Returns 'October'
SELECT DATENAME(weekday, GETDATE()); -- Returns the name of the day of the week (e.g., 'Thursday')

DATEADD()

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

Syntax: DATEADD ( datepart , number , date )

SELECT DATEADD(day, 7, '2023-10-26'); -- Adds 7 days
SELECT DATEADD(month, -2, '2023-10-26'); -- Subtracts 2 months

DATEDIFF()

Returns the difference between two specified dates, counted by the specified datepart (e.g., days, months, years).

Syntax: DATEDIFF ( datepart , startdate , enddate )

SELECT DATEDIFF(day, '2023-10-01', '2023-10-26'); -- Returns 25
SELECT DATEDIFF(year, '2020-01-01', '2023-10-26'); -- Returns 3 (full years passed)

CONVERT()

Converts an expression to a specified data type. It's commonly used to format dates into specific string representations.

Syntax: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

SELECT CONVERT(varchar, GETDATE(), 103); -- Converts to DD/MM/YYYY format (e.g., 26/10/2023)
SELECT CONVERT(varchar, GETDATE(), 101); -- Converts to MM/DD/YYYY format (e.g., 10/26/2023)

FORMAT()

Formats a value (date, number, etc.) into a specified string format. Available from SQL Server 2012 onwards.

Syntax: FORMAT ( value, format [, culture ] )

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss'); -- Formats as YYYY-MM-DD HH:MM:SS
SELECT FORMAT(GETDATE(), 'dd.MM.yyyy'); -- Formats as DD.MM.YYYY

DAY()

Returns the day of the month for a given date.

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

MONTH()

Returns the month part of a date as an integer.

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

YEAR()

Returns the year part of a date as an integer.

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

HOUR()

Returns the hour part of a time or datetime value.

SELECT HOUR(GETDATE());

MINUTE()

Returns the minute part of a time or datetime value.

SELECT MINUTE(GETDATE());

SECOND()

Returns the second part of a time or datetime value.

SELECT SECOND(GETDATE());

CURRENT_TIMESTAMP

An ANSI SQL synonym for GETDATE().

SELECT CURRENT_TIMESTAMP;