jlion.com

SQL Date Handling

Working with dates in SQL Server is straightforward once you know the right types and functions. This page covers the most common patterns: choosing a date type, getting the current time, formatting, arithmetic, extracting parts, building dates, finding boundaries, and filtering date ranges correctly.

Date and Time Data Types

SQL Server has several date/time types. Prefer DATE for date-only values and DATETIME2 over DATETIME for new work.

Type	Range	Precision	Storage
`DATE`	0001-01-01 to 9999-12-31	Day	3 bytes
`TIME`	00:00:00 to 23:59:59.9999999	100ns	3–5 bytes
`DATETIME2`	0001-01-01 to 9999-12-31	100ns	6–8 bytes
`DATETIME`	1753-01-01 to 9999-12-31	~3.33ms	8 bytes
`SMALLDATETIME`	1900-01-01 to 2079-06-06	1 minute	4 bytes
`DATETIMEOFFSET`	0001-01-01 to 9999-12-31	100ns	8–10 bytes

Getting the Current Date and Time

-- Current local datetime (legacy, returns DATETIME)
SELECT GETDATE()

-- Current local datetime (high precision, returns DATETIME2)
SELECT SYSDATETIME()

-- Current UTC datetime
SELECT GETUTCDATE()
SELECT SYSUTCDATETIME()

-- Current datetime with timezone offset
SELECT SYSDATETIMEOFFSET()

-- Date only, time only
SELECT CAST(GETDATE() AS DATE)
SELECT CAST(GETDATE() AS TIME)

Converting and Formatting Dates

Use FORMAT for display strings and CONVERT or CAST when you need to change storage type. Avoid CONVERT with style codes on columns in WHERE clauses — it prevents index use.

-- FORMAT (SQL Server 2012+) — uses .NET format strings
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd')           -- 2026-08-07
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy')           -- 08/07/2026
SELECT FORMAT(GETDATE(), 'MMMM d, yyyy')         -- August 7, 2026
SELECT FORMAT(GETDATE(), 'ddd, MMM d')           -- Thu, Aug 7

-- CONVERT with style codes
SELECT CONVERT(VARCHAR, GETDATE(), 23)           -- 2026-08-07
SELECT CONVERT(VARCHAR, GETDATE(), 101)          -- 08/07/2026
SELECT CONVERT(VARCHAR, GETDATE(), 107)          -- Aug 07, 2026
SELECT CONVERT(VARCHAR, GETDATE(), 120)          -- 2026-08-07 14:30:00

-- CAST — use for type conversion without formatting
SELECT CAST(GETDATE() AS DATE)                   -- strips time
SELECT CAST('2026-08-07' AS DATETIME2)

-- TRY_CONVERT — returns NULL instead of error on bad input
SELECT TRY_CONVERT(DATE, '2026-13-99')           -- NULL (invalid date)
SELECT TRY_CONVERT(DATE, '2026-08-07')           -- 2026-08-07

Date Arithmetic: DATEADD and DATEDIFF

-- DATEADD(part, number, date) — add or subtract an interval
SELECT DATEADD(DAY,    7,  GETDATE())    -- one week from now
SELECT DATEADD(MONTH, -1,  GETDATE())   -- one month ago
SELECT DATEADD(YEAR,   1,  GETDATE())   -- one year from now
SELECT DATEADD(HOUR,   3,  GETDATE())   -- three hours from now

-- DATEDIFF(part, start, end) — integer difference between two dates
SELECT DATEDIFF(DAY,   '2026-01-01', GETDATE())   -- days since Jan 1
SELECT DATEDIFF(MONTH, '2026-01-01', GETDATE())   -- months since Jan 1
SELECT DATEDIFF(YEAR,  '1990-05-12', GETDATE())   -- years since birthdate

-- DATEDIFF_BIG — same but returns BIGINT (for large millisecond ranges)
SELECT DATEDIFF_BIG(MILLISECOND, '2000-01-01', GETDATE())

Extracting Date Parts

-- Convenience functions
SELECT YEAR(GETDATE())       -- 2026
SELECT MONTH(GETDATE())      -- 8
SELECT DAY(GETDATE())        -- 7

-- DATEPART — numeric value for any part
SELECT DATEPART(WEEKDAY, GETDATE())   -- 1=Sunday ... 7=Saturday (@DATEFIRST dependent)
SELECT DATEPART(WEEK,    GETDATE())   -- week number of year
SELECT DATEPART(QUARTER, GETDATE())   -- 1–4
SELECT DATEPART(HOUR,    GETDATE())
SELECT DATEPART(MINUTE,  GETDATE())

-- DATENAME — returns the name as a string
SELECT DATENAME(MONTH,   GETDATE())   -- August
SELECT DATENAME(WEEKDAY, GETDATE())   -- Thursday

Building Dates from Parts

-- DATEFROMPARTS(year, month, day)
SELECT DATEFROMPARTS(2026, 8, 7)                    -- 2026-08-07

-- DATETIME2FROMPARTS(year, month, day, hour, min, sec, fractions, precision)
SELECT DATETIME2FROMPARTS(2026, 8, 7, 14, 30, 0, 0, 0)  -- 2026-08-07 14:30:00

-- DATETIMEFROMPARTS(year, month, day, hour, min, sec, ms)
SELECT DATETIMEFROMPARTS(2026, 8, 7, 14, 30, 0, 0)

-- TIMEFROMPARTS(hour, minute, second, fractions, precision)
SELECT TIMEFROMPARTS(14, 30, 0, 0, 0)               -- 14:30:00

Finding Week and Month Boundaries

-- First day of the current week (Sunday)
SELECT DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE))

-- Last day of the current week (Saturday)
SELECT DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE))

-- First day of the current month
SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)

-- Last day of the current month (EOMONTH, SQL Server 2012+)
SELECT EOMONTH(GETDATE())          -- 2026-08-31
SELECT EOMONTH(GETDATE(), 1)       -- last day of next month
SELECT EOMONTH(GETDATE(), -1)      -- last day of last month

-- First day of the current year
SELECT DATEFROMPARTS(YEAR(GETDATE()), 1, 1)

-- First day of the current quarter
SELECT DATEFROMPARTS(YEAR(GETDATE()), (DATEPART(QUARTER, GETDATE()) - 1) * 3 + 1, 1)

Filtering by Date Range

Avoid wrapping a column in a function in a WHERE clause — it prevents the query engine from using an index on that column. Instead, compute the boundary and filter with a range.

-- BAD: function on the column, index on OrderDate cannot be used
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2026

-- GOOD: range filter, index can be used
SELECT * FROM Orders
WHERE OrderDate >= '2026-01-01'
  AND OrderDate  < '2027-01-01'

-- Filter for a single day (use half-open interval to handle time components)
DECLARE @day DATE = '2026-08-07'
SELECT * FROM Orders
WHERE OrderDate >= @day
  AND OrderDate  < DATEADD(DAY, 1, @day)

-- Filter for the current month
SELECT * FROM Orders
WHERE OrderDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND OrderDate  < DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))

-- Filter for the last 30 days
SELECT * FROM Orders
WHERE OrderDate >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE))

Accounting for Missing Dates in Trend Queries

When charting data by day or week, dates with no rows will be absent from the result unless you explicitly generate the date spine and left join against it.

-- Generate a date spine for the last 30 days using a recursive CTE
DECLARE @start DATE = DATEADD(DAY, -29, CAST(GETDATE() AS DATE))
DECLARE @end   DATE = CAST(GETDATE() AS DATE)

;WITH DateSpine AS (
    SELECT @start AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt) FROM DateSpine WHERE dt < @end
)
SELECT
    d.dt,
    COUNT(o.OrderId) AS OrderCount
FROM DateSpine d
LEFT JOIN Orders o
    ON CAST(o.OrderDate AS DATE) = d.dt
GROUP BY d.dt
ORDER BY d.dt
OPTION (MAXRECURSION 365)

Have an idea to improve this page? Let me know