Date Functions

This section covers built-in date and time functions in SQL Server, which are essential for manipulating and calculating with date and time data types.

Overview

SQL Server provides a rich set of functions to work with dates and times. These functions allow you to extract specific parts of a date, calculate differences between dates, add or subtract intervals, convert between date formats, and much more. Understanding these functions is crucial for building robust and data-driven applications.

Common Date Functions

GETDATE()
Returns the current database system timestamp as a DATETIME2(3) value. This is the standard way to get the current date and time.
DATEPART(datepart, date)
Returns an integer representing the specified part of a specified date. datepart can be year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, etc.
DATENAME(datepart, date)
Returns a character string representing the specified part of a specified date. Similar to DATEPART but returns a string (e.g., "January" for the month).
DATEADD(datepart, number, date)
Returns a new date that results from adding or subtracting a specified time interval to a specified date. number can be positive or negative.
DATEDIFF(datepart, startdate, enddate)
Returns the count of the specified datepart boundaries crossed between two specified dates. Useful for calculating durations.
GETUTCDATE()
Returns the current UTC (Coordinated Universal Time) database system timestamp as a DATETIME2(3) value.
SYSDATETIME()
Returns the current database system timestamp as a DATETIME2(7) value. Offers higher precision than GETDATE().
ISDATE(expression)
Tests whether an expression is a valid date. Returns 1 if valid, 0 if not.
YEAR(date)
Returns the year part of a date as an integer.
MONTH(date)
Returns the month part of a date as an integer (1-12).
DAY(date)
Returns the day part of a date as an integer (1-31).

Examples

Getting Current Date and Time

To retrieve the current date and time:

SELECT GETDATE();
SELECT SYSDATETIME();

Extracting Date Parts

To get the year, month, and day from a specific date:

DECLARE @MyDate DATE = '2023-10-27';
SELECT
    YEAR(@MyDate) AS Year,
    MONTH(@MyDate) AS Month,
    DAY(@MyDate) AS Day,
    DATEPART(weekday, @MyDate) AS DayOfWeek;

Output might look like:

Year | Month | Day | DayOfWeek
-----|-------|-----|----------
2023 | 10    | 27  | 6

(Note: DayOfWeek can vary based on `DATEFIRST` setting.)

Adding or Subtracting Dates

To find the date one month from now, or 10 days ago:

SELECT DATEADD(month, 1, GETDATE()) AS OneMonthFromNow;
SELECT DATEADD(day, -10, GETDATE()) AS TenDaysAgo;

Calculating Date Differences

To find the number of days between two dates:

DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-10-27';
SELECT DATEDIFF(day, @StartDate, @EndDate) AS DaysDifference;

Formatting Dates

Using CONVERT for date formatting:

SELECT CONVERT(VARCHAR, GETDATE(), 103) AS FormattedDate_DDMMYYYY; -- DD/MM/YYYY
SELECT CONVERT(VARCHAR, GETDATE(), 101) AS FormattedDate_MMDDYYYY; -- MM/DD/YYYY
SELECT CONVERT(VARCHAR, GETDATE(), 120) AS FormattedDate_YYYYMMDD_HHMMSS; -- YYYY-MM-DD HH:MM:SS

Best Practices

Use the most appropriate date/time data type for your needs (e.g., DATE, TIME, DATETIME2).
Be mindful of time zones and use GETUTCDATE() when necessary.
Understand the effect of the DATEFIRST setting on functions like DATENAME(weekday, ...).
Use CONVERT or FORMAT (SQL Server 2012+) for consistent date string formatting.

SQL Server Docs