Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 39 ยท Functions and Expressions

SQL Server Date and Time Functions

Work with dates in SQL Server: SYSDATETIME, DATEADD, DATEDIFF, DATEPART, EOMONTH, DATETRUNC, time zones and safe date range filters, with portable stand-ins.

Dates are where bugs hide: off-by-one boundaries, time zones, midnight, month-ends. T-SQL gives you a rich, consistent set of date functions built around a few ideas: get the current time, add an interval, measure the distance between two dates, and extract or truncate a part. This lesson explains each, shows the traps, and ends with patterns you will reuse in reports.

Date arithmetic in the live editor uses SQLite's date() and strftime(), so each T-SQL example has a labelled stand-in where it makes sense.

Getting the current time

Function Returns
SYSDATETIME() local server time as DATETIME2(7)
SYSUTCDATETIME() UTC time as DATETIME2(7)
SYSDATETIMEOFFSET() local time with offset
GETDATE(), CURRENT_TIMESTAMP local time as legacy DATETIME (3 ms precision)
GETUTCDATE() UTC time as DATETIME
CAST(SYSDATETIME() AS DATE) today's date

Store timestamps in UTC (SYSUTCDATETIME()), convert to local time only for display, and you avoid daylight-saving headaches.

Date parts: DATEPART, DATENAME, YEAR, MONTH, DAY

SQL
SELECT YEAR('2025-03-14')          AS y,   -- 2025
       MONTH('2025-03-14')         AS m,   -- 3
       DATEPART(quarter, '2025-03-14') AS q,   -- 1
       DATEPART(weekday, '2025-03-14') AS wd,  -- depends on SET DATEFIRST
       DATENAME(month, '2025-03-14')   AS month_name; -- March

DATEPART returns a number, DATENAME the text name. Day-of-week numbers depend on SET DATEFIRST (default 7 = Sunday for US English), so avoid hard-coding them. Also available: year, quarter, month, dayofyear, day, week, iso_week, weekday, hour, minute, second, millisecond, microsecond, nanosecond.

SQL Stand-in: extract date parts ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Adding and subtracting: DATEADD

DATEADD(part, number, date) adds (or, with a negative number, subtracts) an interval. It handles month lengths and leap years correctly:

SQL
SELECT DATEADD(day,   30, '2025-01-31') AS plus_30_days,    -- 2025-03-02
       DATEADD(month,  1, '2025-01-31') AS plus_one_month,  -- 2025-02-28 (clamped)
       DATEADD(year,  -1, '2025-02-28') AS last_year;       -- 2024-02-28

Adding a month to January 31 does not give "February 31"; SQL Server clamps to the last day of the month. It also supports hour, minute, and so on, when the type has a time portion.

SQL Stand-in: DATEADD with date modifiers ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Measuring: DATEDIFF

DATEDIFF(part, start, end) returns the number of boundaries of that part crossed between the two dates, as an INT:

SQL
SELECT DATEDIFF(day,   '2025-03-01', '2025-03-15') AS days,     -- 14
       DATEDIFF(month, '2025-01-31', '2025-02-01') AS months,   -- 1 (a month boundary was crossed!)
       DATEDIFF(year,  '2024-12-31', '2025-01-01') AS years;    -- 1

The second and third lines are the famous trap: one day apart, yet one month and one year, because a boundary was crossed. To compute someone's exact age or the number of full months, use extra logic (for example comparing with DATEADD). For very long spans, DATEDIFF can overflow INT; use DATEDIFF_BIG for seconds or milliseconds over many years.

SQL Stand-in: days between two dates ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Month-ends and truncation

  • EOMONTH(date [, months]) (2012+) returns the last day of the month; the optional second argument shifts months.
  • DATEFROMPARTS(y, m, d) builds a date from numbers.
  • DATETRUNC(part, date) (2022+) cuts a date down to the start of the year, month, week and so on.
  • Before 2022, the usual first-of-month trick was DATEADD(month, DATEDIFF(month, 0, d), 0). It works because DATEDIFF counts month boundaries from date 0 and DATEADD adds them back.
SQL
SELECT EOMONTH('2025-02-10')           AS month_end,       -- 2025-02-28
       DATETRUNC(month, '2025-02-10')  AS month_start,     -- 2025-02-01 (2022+)
       DATEFROMPARTS(2025, 12, 25)     AS christmas;

Safe date filtering and grouping

For "everything in March 2025", use a half-open range so every time-of-day is included and an index can be used:

SQL
WHERE ordered_at >= '20250301' AND ordered_at < DATEADD(month, 1, '20250301')

To group by month without calling functions on the filter column, group on the truncated value:

SQL Group orders by month ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Time zones

SYSDATETIMEOFFSET() and DATETIMEOFFSET carry an offset. AT TIME ZONE converts between zones using the Windows time zone names, and it handles daylight saving:

SQL
SELECT SYSUTCDATETIME() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' AS pacific_time;

The first AT TIME ZONE 'UTC' marks the value as UTC; the second converts. List the valid names in sys.time_zone_info.

Parsing and validating

TRY_CONVERT(date, text) returns NULL instead of an error for invalid input; ISDATE(text) returns 1 or 0 (affected by language settings). SET DATEFORMAT and SET LANGUAGE change how ambiguous strings like 03/04/2025 are read, so prefer the unambiguous literal '20250304' or ISO '2025-03-04T00:00:00'. Note that '2025-03-04' with the legacy DATETIME type may still be read as year-day-month under some language settings; DATE and DATETIME2 always read ISO format correctly.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does DATEDIFF(month, '2025-01-31', '2025-02-01') return?

    DATEDIFF counts how many month boundaries are crossed, and the first day of February crosses one even though only a day passed.
  2. 2. Which function returns the last day of the month?

    EOMONTH(date) returns the last day of the month for the given date; an optional second argument shifts months.
  3. 3. What is the best type of timestamp to store for events from many time zones?

    Storing UTC (SYSUTCDATETIME) avoids daylight-saving ambiguity; convert to local time when you display it.
  4. 4. What does DATEADD(month, 1, '2025-01-31') return?

    T-SQL clamps to the last valid day of the resulting month.

Summary

  • SYSDATETIME() and SYSUTCDATETIME() return precise current times; store UTC.
  • DATEPART/DATENAME extract parts; DATEADD shifts dates; DATEDIFF counts boundaries crossed.
  • EOMONTH, DATEFROMPARTS and (2022+) DATETRUNC build month starts and ends.
  • Filter dates with half-open ranges, and use TRY_CONVERT for untrusted input.
  • AT TIME ZONE converts between zones with daylight-saving rules.

Next: Conversion and Numeric Functions