SQL Date and Time Functions

SQL provides a rich set of functions to manipulate and extract information from date and time data types. These functions are essential for performing time-based analysis, scheduling, and managing temporal data.

Date and Time Functions Overview

The following are some of the most commonly used date and time functions available in SQL:

Getting Current Date and Time

GETDATE(): Returns the current database system date and time.
CURRENT_TIMESTAMP: Returns the current date and time according to the SQL standard.
SYSDATETIME(): Returns the date and time of the server.

Example:

SELECT GETDATE() AS CurrentDateTime;

Extracting Date Parts

These functions allow you to extract specific components (year, month, day, hour, etc.) from a date or time value.

YEAR(date): Extracts the year from a date.
MONTH(date): Extracts the month from a date.
DAY(date): Extracts the day of the month from a date.
HOUR(time): Extracts the hour from a time.
MINUTE(time): Extracts the minute from a time.
SECOND(time): Extracts the second from a time.
DATEPART(datepart, date): Returns an integer representing the specified date part of the specified date. (e.g., DATEPART(year, '2023-10-27') returns 2023).
DATENAME(datepart, date): Returns a character string representing the specified date part of the specified date. (e.g., DATENAME(month, '2023-10-27') returns 'October').

Example:

SELECT
    YEAR('2023-10-27 10:30:00') AS YearPart,
    MONTH('2023-10-27 10:30:00') AS MonthPart,
    DAY('2023-10-27 10:30:00') AS DayPart;

Date Manipulation

These functions are used to add or subtract intervals from dates, or to calculate the difference between two dates.

DATEADD(datepart, number, date): Adds a specified time interval to a date.
DATEDIFF(datepart, startdate, enddate): Returns the difference between two dates, in the specified date part.
GETUTCDATE(): Returns the current UTC date and time.

Example:

SELECT
    DATEADD(day, 7, '2023-10-27') AS SevenDaysLater,
    DATEDIFF(month, '2023-01-01', '2023-10-27') AS MonthsDifference;

Formatting Dates

Convert dates and times into various string formats.

FORMAT(date, format[, culture]): Formats a date value into a string according to a specified format.
CONVERT(datatype, expression[, style]): Converts an expression from one data type to another. Useful for date formatting using style codes.

Example:

SELECT
    FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS FormattedDateTime,
    CONVERT(VARCHAR, GETDATE(), 103) AS FormattedDDMMYYYY;

Date Comparison and Validation

ISDATE(expression): Returns 1 if the expression is a valid date, and 0 if it is not.
CHECKSUM_AGG(expression): Calculates a checksum for each group in a query or for the entire set. Can be used indirectly for date integrity checks.