SQL Server Date Functions Cheat Sheet

SQL Server has a lot of built-in date functions. Some you’ll use constantly, some only in specific situations. This cheat sheet covers all the important ones with syntax, examples, and enough context to know when to use each one.

Getting the Current Date and Time

These functions return the current date and/or time at the moment the query runs.

Function	Returns	Example Output
`GETDATE()`	Current date and time (DATETIME)	2026-06-24 09:15:30.123
`GETUTCDATE()`	Current UTC date and time (DATETIME)	2026-06-24 01:15:30.123
`SYSDATETIME()`	Current date and time (DATETIME2, higher precision)	2026-06-24 09:15:30.1234567
`SYSUTCDATETIME()`	Current UTC date and time (DATETIME2)	2026-06-24 01:15:30.1234567
`SYSDATETIMEOFFSET()`	Current date, time, and timezone offset (DATETIMEOFFSET)	2026-06-24 09:15:30.1234567 +08:00

GETDATE() is the one you’ll use most often. Use SYSDATETIME() when you need higher precision, and SYSDATETIMEOFFSET() when timezone context matters.

Extracting Parts of a Date

These functions pull a specific component out of a date or datetime value.

`YEAR()`, `MONTH()`, `DAY()`

The simplest way to extract a year, month, or day as an integer.

SELECT 
  YEAR(GETDATE()) AS Year,
  MONTH(GETDATE()) AS Month,
  DAY(GETDATE()) AS Day;

Result:

Year        Month       Day        
----------- ----------- -----------
       2026           6          26

`DATEPART()`

DATEPART() is more flexible. It lets you extract any part of a date using a datepart argument, including things YEAR(), MONTH(), and DAY() can’t reach, like quarter, week, hour, or minute.

SELECT
    DATEPART(quarter, GETDATE()) AS Quarter,
    DATEPART(week, GETDATE())    AS Week,
    DATEPART(hour, GETDATE())    AS Hour,
    DATEPART(minute, GETDATE())  AS Minute;

Result:

Quarter     Week        Hour        Minute     
----------- ----------- ----------- -----------
          2          26           4          58

DATENAME()

DATENAME() works like DATEPART() but returns a string instead of an integer. Useful when you want the name of a month or weekday rather than its number.

SELECT
    DATENAME(month, GETDATE())   AS MonthName,
    DATENAME(weekday, GETDATE()) AS WeekdayName;

Result:

MonthName                      WeekdayName                   
------------------------------ ------------------------------
June                           Saturday

Common Datepart Values

Both DATEPART() and DATENAME() use the same set of datepart arguments. Here are the ones you’ll use most:

Datepart	Abbreviation	Description
year	yy, yyyy	Year
quarter	qq, q	Quarter (1 to 4)
month	mm, m	Month (1 to 12)
dayofyear	dy, y	Day of the year (1 to 366)
day	dd, d	Day of the month
week	wk, ww	Week number
weekday	dw	Day of the week (1 to 7)
hour	hh	Hour
minute	mi, n	Minute
second	ss, s	Second
millisecond	ms	Millisecond

Adding and Subtracting Dates

`DATEADD()`

DATEADD() adds or subtracts a specified amount of time from a date. Pass in the datepart, the number to add (negative to subtract), and the date.

SELECT
    DATEADD(day, 7, GETDATE())    AS SevenDaysFromNow,
    DATEADD(month, -1, GETDATE()) AS OneMonthAgo,
    DATEADD(year, 1, GETDATE())   AS ThisDateNextYear,
    DATEADD(hour, 3, GETDATE())   AS ThreeHoursFromNow;

Result:

SevenDaysFromNow        OneMonthAgo             ThisDateNextYear        ThreeHoursFromNow      
----------------------- ----------------------- ----------------------- -----------------------
2026-07-04 01:56:48.060 2026-05-27 01:56:48.060 2027-06-27 01:56:48.060 2026-06-27 04:56:48.060

Calculating the Difference Between Dates

`DATEDIFF()`

DATEDIFF() returns the difference between two dates in whatever unit you specify. The result is always an integer.

SELECT
    DATEDIFF(day, '2026-01-01', GETDATE())            AS DaysSinceJan1,
    DATEDIFF(month, '2025-06-24', GETDATE())          AS MonthsBetween,
    DATEDIFF(year, '1990-03-15', GETDATE())           AS AgeInYears,
    DATEDIFF(minute, '2026-06-24 08:00', GETDATE())   AS MinutesElapsed;

Result:

DaysSinceJan1 MonthsBetween AgeInYears  MinutesElapsed
------------- ------------- ----------- --------------
          177            12          36           3957

One thing worth knowing: DATEDIFF() counts boundary crossings, not elapsed time. DATEDIFF(year, '2025-12-31', '2026-01-01') returns 1, even though only one day has passed. Keep that in mind when calculating ages or durations.

`DATEDIFF_BIG()`

When working with large values, you may need to switch to DATEDIFF_BIG(). This function works exactly like DATEDIFF() but returns a BIGINT instead of an INT.

This option is only needed when the difference is large enough to overflow a regular integer, which typically only happens when measuring in milliseconds or smaller units over long time spans. For example, getting the number of seconds since 1950 will result in the following error with DATEDIFF():

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

But DATEDIFF_BIG() will handle it perfectly:

2398377600

Integers have a range from -2,147,483,648 to 2,147,483,647 whereas big integers have a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Formatting Dates

`CONVERT()`

Converts a date to a string using a numeric style code. Fast and well-suited for standard formats.

SELECT
    CONVERT(VARCHAR, GETDATE(), 103) AS UKFormat,
    CONVERT(VARCHAR, GETDATE(), 120) AS DateTimeFormat;

Result:

UKFormat                       DateTimeFormat                
------------------------------ ------------------------------
27/06/2026                     2026-06-27 02:08:01

`FORMAT()`

Converts a date to a string using a .NET format string. More flexible than CONVERT() but slower on large datasets.

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

Result:

LongDate           DateTimeFormat 
--------------   ------------------
27 June 2026   2026-06-27 02:09:40

Converting Strings to Dates

`CAST()` and `CONVERT()`

Both can convert a string into a date type. CONVERT() lets you specify the format of the incoming string using a style code, which can be useful when the string format might be ambiguous.

SELECT
    CAST('2026-06-24' AS DATE)          AS CastResult,
    CONVERT(DATE, '24/06/2026', 103)    AS ConvertResult;

Result:

CastResult       ConvertResult   
---------------- ----------------
      2026-06-24       2026-06-24

`TRY_CAST()` and `TRY_CONVERT()`

These are safer versions of CAST() and CONVERT(). Instead of throwing an error when a conversion fails, they return NULL. Useful when your data isn’t guaranteed to be clean.

SELECT
    TRY_CAST('not a date' AS DATE)       AS TryCastResult,
    TRY_CONVERT(DATE, 'not a date', 103) AS TryConvertResult;

Result:

TryCastResult    TryConvertResult
---------------- ----------------
            NULL             NULL

Truncating Dates

`DATETRUNC()`

Introduced in SQL Server 2022, DATETRUNC() truncates a date to the start of a specified unit. If you want the first day of the current month, or the start of the current year, this is the cleanest way to do it.

SELECT
    GETDATE() AS CurrentDateTime,
    DATETRUNC(month, GETDATE()) AS StartOfMonth,
    DATETRUNC(year, GETDATE())  AS StartOfYear,
    DATETRUNC(week, GETDATE())  AS StartOfWeek;

Result:

CurrentDateTime         StartOfMonth            StartOfYear             StartOfWeek            
----------------------- ----------------------- ----------------------- -----------------------
2026-06-27 02:18:22.243 2026-06-01 00:00:00.000 2026-01-01 00:00:00.000 2026-06-21 00:00:00.000

Before SQL Server 2022, the common workaround was to combine DATEADD() and DATEDIFF():

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS StartOfMonth;

Result:

StartOfMonth           
-----------------------
2026-06-01 00:00:00.000

It works, but DATETRUNC() is much easier to read if your SQL Server version supports it.

Other Useful Date Functions

Function	Description	Example
`EOMONTH(date)`	Returns the last day of the month for a given date	`EOMONTH(GETDATE())` → 2026-06-30
`EOMONTH(date, n)`	Returns the last day of the month n months away	`EOMONTH(GETDATE(), 1)` → 2026-07-31
`ISDATE(value)`	Returns 1 if the value is a valid date, 0 if not	`ISDATE('2026-06-24')` → 1
`SWITCHOFFSET(dto, offset)`	Converts a DATETIMEOFFSET to a different timezone offset	`SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00')`
`TODATETIMEOFFSET(dt, offset)`	Adds a timezone offset to a datetime value	`TODATETIMEOFFSET(GETDATE(), '+08:00')`
`AT TIME ZONE`	Converts a datetime to a specified timezone (SQL Server 2016+)	`GETDATE() AT TIME ZONE 'UTC'`

Quick Reference

Task	Function
Get the current date and time	`GETDATE()` or `SYSDATETIME()`
Get the current UTC time	`GETUTCDATE()` or `SYSUTCDATETIME()`
Extract year, month, or day	`YEAR()`, `MONTH()`, `DAY()`
Extract any date component	`DATEPART()`
Get a month or weekday name	`DATENAME()`
Add or subtract time	`DATEADD()`
Find the difference between dates	`DATEDIFF()`
Format a date as a string	`CONVERT()` or `FORMAT()`
Convert a string to a date	`CAST()` or `CONVERT()`
Safely convert without errors	`TRY_CAST()` or `TRY_CONVERT()`
Truncate to start of period	`DATETRUNC()` (SQL Server 2022+)
Get the last day of a month	`EOMONTH()`
Check if a value is a valid date	`ISDATE()`

Getting the Current Date and Time

Extracting Parts of a Date

YEAR(), MONTH(), DAY()

DATEPART()

DATENAME()

Common Datepart Values

Adding and Subtracting Dates

DATEADD()

Calculating the Difference Between Dates

DATEDIFF()

DATEDIFF_BIG()

Formatting Dates

CONVERT()

FORMAT()

Converting Strings to Dates

CAST() and CONVERT()

TRY_CAST() and TRY_CONVERT()

Truncating Dates

DATETRUNC()

Other Useful Date Functions

Quick Reference

`YEAR()`, `MONTH()`, `DAY()`

`DATEPART()`

`DATEADD()`

`DATEDIFF()`

`DATEDIFF_BIG()`

`CONVERT()`

`FORMAT()`

`CAST()` and `CONVERT()`

`TRY_CAST()` and `TRY_CONVERT()`

`DATETRUNC()`