<style> .reveal-up, .reveal-left, .reveal-right, .reveal-stagger > * { opacity: 1 !important; transform: none !important; } </style>
  1. Learn
  2. SQL
  3. Functions and expressions
All lessons in this course

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Lesson 15 of 54 16 minBeginner

Dates and times

Take dates apart, add to them, measure between them and build them from parts. Plus why DATEDIFF counts boundaries, how to filter by month properly, and how to handle time zones.

Orders, deliveries, invoices and memberships all hang off dates. T-SQL has a full set of date functions; most of them take a datepart argument such as year, month, day, week, hour or minute.

The current date and time

Now.sql
SELECT SYSDATETIME()       AS LocalNow,       -- datetime2(7), the server's clock       SYSDATETIMEOFFSET() AS LocalWithZone,  -- datetimeoffset       SYSUTCDATETIME()    AS UtcNow,       CAST(SYSDATETIME() AS date) AS Today,       GETDATE()           AS OlderStyle;     -- datetime, less precise

No output is shown because yours will differ. Every sample below uses fixed dates, so you get exactly the results printed.

Taking dates apart

Query.sql
SELECT OrderId, OrderDate,       YEAR(OrderDate) AS Yr,       MONTH(OrderDate) AS Mon,       DATENAME(month, OrderDate) AS MonthName,       DATENAME(weekday, OrderDate) AS DayName,       DATEPART(quarter, OrderDate) AS QtrFROM dbo.OrdersWHERE OrderId IN (1, 4, 8);
Output
OrderId  OrderDate     Yr  Mon  MonthName  DayName   Qtr
-------  ----------  ----  ---  ---------  --------  ---
      1  2024-02-03  2024    2  February   Saturday    1
      4  2024-03-15  2024    3  March      Friday      1
      8  2024-05-18  2024    5  May        Saturday    2

Adding to dates

DATEADD(part, n, date) moves a date forward (or back, with a negative n). Adding a month to 31 January lands on the last day of February, because there is no 31 February:

Query.sql
DECLARE @d date = '2024-01-31'; SELECT DATEADD(day, 30, @d)    AS Plus30Days,       DATEADD(month, 1, @d)   AS PlusOneMonth,       DATEADD(month, -2, @d)  AS MinusTwoMonths,       DATEADD(year, 1, CAST('2024-02-29' AS date)) AS LeapDayPlusYear;
Output
Plus30Days  PlusOneMonth  MinusTwoMonths  LeapDayPlusYear
----------  ------------  --------------  ---------------
2024-03-01  2024-02-29    2023-11-30      2025-02-28

Measuring between dates

DATEDIFF(part, start, end) does not measure whole periods. It counts how many period boundaries lie between the dates: midnights for days, 1 Januaries for years. See it on a timeline:

Interactive

DATEDIFF counts boundaries

20242023-12-312024-01-01
DATEDIFF(year, '2023-12-31', '2024-01-01') →1
One day apart, yet DATEDIFF(year) is 1: the dates are on either side of 1 January. DATEDIFF counts boundaries crossed, not whole years. To get someone’s age, you have to check whether the birthday has happened yet this year.
Query.sql
SELECT DATEDIFF(year, '2023-12-31', '2024-01-01') AS YearsOneDayApart,       DATEDIFF(year, '2024-01-01', '2024-12-31') AS YearsAlmostAYear,       DATEDIFF(day,  '2024-02-03', '2024-07-01') AS Days;
Output
YearsOneDayApart  YearsAlmostAYear  Days
----------------  ----------------  ----
               1                 0   149

For whole years, as in an age or a length of membership, subtract one when this year’s anniversary has not arrived yet:

Query.sql
DECLARE @asOf date = '2024-09-01'; SELECT FirstName, JoinedOn,       DATEDIFF(year, JoinedOn, @asOf) AS BoundaryCount,       DATEDIFF(year, JoinedOn, @asOf)         - IIF(DATEADD(year, DATEDIFF(year, JoinedOn, @asOf), JoinedOn) > @asOf, 1, 0) AS WholeYearsFROM dbo.CustomersWHERE CustomerId IN (1, 4, 6);
Output
FirstName  JoinedOn    BoundaryCount  WholeYears
---------  ----------  -------------  ----------
Asha       2023-03-02              1           1
Dev        2023-08-23              1           1
Farid      2023-11-30              1           0

Building dates

Query.sql
DECLARE @d date = '2024-02-10'; SELECT DATEFROMPARTS(YEAR(@d), MONTH(@d), 1) AS FirstOfMonth,       EOMONTH(@d) AS LastOfMonth,       EOMONTH(@d, 1) AS LastOfNextMonth,       DATEFROMPARTS(2024, 12, 25) AS Christmas;
Output
FirstOfMonth  LastOfMonth  LastOfNextMonth  Christmas
------------  -----------  ---------------  ----------
2024-02-01    2024-02-29   2024-03-31       2024-12-25

Filtering by a month: use a range

Two ways to find May 2024’s orders return the same rows:

Query.sql
-- 1. Functions on the columnSELECT OrderId, OrderDate FROM dbo.OrdersWHERE YEAR(OrderDate) = 2024 AND MONTH(OrderDate) = 5; -- 2. A half-open range: from the 1st, up to (not including) the next 1stSELECT OrderId, OrderDate FROM dbo.OrdersWHERE OrderDate >= '2024-05-01' AND OrderDate < '2024-06-01';
Output
OrderId  OrderDate
-------  ----------
      7  2024-05-05
      8  2024-05-18

OrderId  OrderDate
-------  ----------
      7  2024-05-05
      8  2024-05-18

Prefer the second. Wrapping the column in a function means SQL Server must calculate it for every row, so an index on OrderDate cannot be used to jump to May. The range also works unchanged if the column later gains a time of day. The Indexes module calls this being sargable.

Formatting for display

Query.sql
SELECT OrderDate,       CONVERT(varchar(10), OrderDate, 103) AS British,       CONVERT(varchar(10), OrderDate, 101) AS American,       FORMAT(OrderDate, 'dddd d MMMM yyyy', 'en-GB') AS LongFROM dbo.OrdersWHERE OrderId = 1;
Output
OrderDate   British     American    Long
----------  ----------  ----------  ------------------------
2024-02-03  03/02/2024  02/03/2024  Saturday 3 February 2024

Time zones

datetime2 has no time zone. AT TIME ZONE attaches one, using Windows zone names, and handles summer time for you. Here a UK delivery slot is converted to UTC and to India time:

Query.sql
DECLARE @slot datetime2(0) = '2024-06-12 14:30:00'; SELECT @slot AT TIME ZONE 'GMT Standard Time' AS UkTime,       @slot AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC' AS Utc,       @slot AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'India Standard Time' AS India;
Output
UkTime                      Utc                         India
--------------------------  --------------------------  --------------------------
2024-06-12 14:30:00 +01:00  2024-06-12 13:30:00 +00:00  2024-06-12 19:00:00 +05:30
Quick check

What is DATEDIFF(month, '2024-01-31', '2024-02-01')?

Quick check

Why is WHERE OrderDate >= '2024-05-01' AND OrderDate < '2024-06-01' better than WHERE MONTH(OrderDate) = 5 AND YEAR(OrderDate) = 2024?

Practice

For each Shipped order, show the OrderId, the OrderDate, the payment due date 30 days later as DueDate, and the last day of the order’s month as MonthEnd.

Show a hint

DATEADD(day, 30, OrderDate) and EOMONTH(OrderDate). Filter on Status.

Show the solution
Solution.sql
SELECT OrderId, OrderDate,       DATEADD(day, 30, OrderDate) AS DueDate,       EOMONTH(OrderDate) AS MonthEndFROM dbo.OrdersWHERE Status = 'Shipped';
Output
OrderId  OrderDate   DueDate     MonthEnd
-------  ----------  ----------  ----------
      1  2024-02-03  2024-03-04  2024-02-29
      2  2024-02-10  2024-03-11  2024-02-29
      3  2024-03-01  2024-03-31  2024-03-31
      4  2024-03-15  2024-04-14  2024-03-31
      5  2024-04-02  2024-05-02  2024-04-30
      7  2024-05-05  2024-06-04  2024-05-31
     10  2024-06-12  2024-07-12  2024-06-30

Remember

  • YEAR, MONTH, DATEPART and DATENAME take dates apart; DATEFROMPARTS and EOMONTH build them.
  • DATEADD adds whole parts and clips to the end of shorter months.
  • DATEDIFF counts boundaries crossed, not whole periods: one day can be “1 year”.
  • Filter date ranges with >= start AND < next start, not functions on the column.
  • Format only for display; keep dates as dates. Use datetimeoffset or AT TIME ZONE when time zones matter.
0 of 54 lessons completed