SQL Server Date & Datetime Functions

Quick reference for T-SQL date and time functions. Covers all major data types: DATE, TIME, DATETIME, DATETIME2, DATETIMEOFFSET, and SMALLDATETIME.

Date/Time Data Types

Type	Range	Precision	Storage
`DATE`	0001-01-01 – 9999-12-31	1 day	3 bytes
`TIME(n)`	00:00:00 – 23:59:59.9999999	100ns	3–5 bytes
`SMALLDATETIME`	1900-01-01 – 2079-06-06	1 minute	4 bytes
`DATETIME`	1753-01-01 – 9999-12-31	3.33ms	8 bytes
`DATETIME2(n)`	0001-01-01 – 9999-12-31	100ns	6–8 bytes
`DATETIMEOFFSET(n)`	Same as DATETIME2 + UTC offset	100ns	8–10 bytes

Best practice: Use DATETIME2 for new development — it has wider range, higher precision, and is ANSI-compliant. Avoid DATETIME for new tables.

Current Date & Time

Function	Returns	Type
`GETDATE()`	Current local date and time	`DATETIME`
`SYSDATETIME()`	Current local date and time (higher precision)	`DATETIME2(7)`
`GETUTCDATE()`	Current UTC date and time	`DATETIME`
`SYSUTCDATETIME()`	Current UTC date and time (higher precision)	`DATETIME2(7)`
`SYSDATETIMEOFFSET()`	Current date/time with UTC offset	`DATETIMEOFFSET(7)`
`CAST(GETDATE() AS DATE)`	Today's date only	`DATE`
`CAST(GETDATE() AS TIME)`	Current time only	`TIME`

Extracting Date Parts

Function	Description	Example	Result
`YEAR(date)`	Year as integer	`YEAR('2024-03-15')`	`2024`
`MONTH(date)`	Month (1–12)	`MONTH('2024-03-15')`	`3`
`DAY(date)`	Day of month (1–31)	`DAY('2024-03-15')`	`15`
`DATEPART(part, date)`	Any date part as integer	`DATEPART(quarter, '2024-03-15')`	`1`
`DATENAME(part, date)`	Date part as string	`DATENAME(month, '2024-03-15')`	`'March'`
`DATEPART(weekday, date)`	Day of week (1=Sun by default)	`DATEPART(weekday, '2024-03-15')`	`6` (Friday)
`DATEPART(week, date)`	Week of year (1–53)	`DATEPART(week, '2024-03-15')`	`11`
`DATEPART(hour, dt)`	Hour (0–23)	`DATEPART(hour, '2024-03-15 14:30:00')`	`14`

Common DATEPART values: year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, nanosecond, tzoffset, iso_week.

Date Arithmetic

Function	Description	Example	Result
`DATEADD(part, n, date)`	Add n units to date	`DATEADD(month, 3, '2024-01-15')`	`2024-04-15`
`DATEADD(day, -7, date)`	Subtract 7 days	`DATEADD(day, -7, '2024-03-15')`	`2024-03-08`
`DATEDIFF(part, start, end)`	Difference in specified units	`DATEDIFF(day, '2024-01-01', '2024-03-15')`	`74`
`DATEDIFF_BIG(part, start, end)`	Like DATEDIFF but returns BIGINT	`DATEDIFF_BIG(second, '2000-01-01', GETDATE())`	large number
`EOMONTH(date)`	Last day of the month	`EOMONTH('2024-02-10')`	`2024-02-29`
`EOMONTH(date, n)`	Last day of month + n months	`EOMONTH('2024-01-01', 1)`	`2024-02-29`

DATEDIFF counts boundary crossings, not exact elapsed time. DATEDIFF(year,'2024-12-31','2025-01-01') returns 1 even though only 1 day apart.

Formatting & Conversion

Function	Description	Example	Result
`FORMAT(date, fmt)`	Format using .NET format strings	`FORMAT(GETDATE(), 'yyyy-MM-dd')`	`'2024-03-15'`
`FORMAT(date, 'D', 'de-DE')`	Locale-aware long date	German locale	`'Freitag, 15. März 2024'`
`CONVERT(VARCHAR, date, style)`	Format with style code	`CONVERT(VARCHAR, GETDATE(), 103)`	`'15/03/2024'` (British)
`CONVERT(VARCHAR, date, 120)`	ISO 8601 datetime		`'2024-03-15 14:30:00'`
`CONVERT(VARCHAR, date, 23)`	ISO date only		`'2024-03-15'`

Common CONVERT style codes: 101=US (mm/dd/yyyy), 103=British (dd/mm/yyyy), 104=German (dd.mm.yyyy), 120=ISO datetime, 23=ISO date. FORMAT is more flexible but slower — avoid in large queries.

Parsing Strings to Dates

Function	Description	Example	Result
`CAST('2024-03-15' AS DATE)`	Parse ISO string		`2024-03-15`
`CONVERT(DATE, '15/03/2024', 103)`	Parse with style code	British format	`2024-03-15`
`TRY_CAST(str AS DATE)`	Returns NULL on failure	`TRY_CAST('notadate' AS DATE)`	`NULL`
`TRY_CONVERT(DATE, str, style)`	Safe convert with style		NULL if fails
`PARSE(str AS DATE USING culture)`	Culture-aware parse	`PARSE('15 März 2024' AS DATE USING 'de-DE')`	`2024-03-15`

Constructing Dates

Function	Description	Example	Result
`DATEFROMPARTS(y, m, d)`	Build DATE from parts	`DATEFROMPARTS(2024, 3, 15)`	`2024-03-15`
`DATETIME2FROMPARTS(y,mo,d,h,mi,s,frac,prec)`	Build DATETIME2 from parts
`TIMEFROMPARTS(h, m, s, frac, prec)`	Build TIME from parts	`TIMEFROMPARTS(14, 30, 0, 0, 0)`	`14:30:00`
`SMALLDATETIMEFROMPARTS(y,mo,d,h,mi)`	Build SMALLDATETIME from parts

Common Patterns

-- Start of current month
SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)

-- End of current month
SELECT EOMONTH(GETDATE())

-- Start of current year
SELECT DATEFROMPARTS(YEAR(GETDATE()), 1, 1)

-- Truncate to hour (remove minutes/seconds)
SELECT DATEADD(hour, DATEDIFF(hour, 0, GETDATE()), 0)

-- Age in years
SELECT DATEDIFF(year, birthdate, GETDATE())
     - CASE WHEN DATEADD(year, DATEDIFF(year, birthdate, GETDATE()), birthdate) > GETDATE()
            THEN 1 ELSE 0 END

-- Business days between two dates (approximate, no holidays)
SELECT DATEDIFF(day, start_date, end_date)
     - DATEDIFF(week, start_date, end_date) * 2
     - CASE WHEN DATEPART(weekday, start_date) = 1 THEN 1 ELSE 0 END
     + CASE WHEN DATEPART(weekday, end_date) = 1 THEN 1 ELSE 0 END

-- ISO week number
SELECT DATEPART(iso_week, GETDATE())

-- Filter current week's records
WHERE date_col >= DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)
  AND date_col <  DATEADD(week, DATEDIFF(week, 0, GETDATE()) + 1, 0)

-- Safe date range filter (avoid implicit conversions)
WHERE date_col >= CAST('2024-01-01' AS DATE)
  AND date_col <  CAST('2024-04-01' AS DATE)