Date Functions

Date Functions

What it means

SQL Server provides built-in functions for getting the current date/time, extracting parts of a date, calculating differences, and formatting output — GETDATE(), DATEADD(), DATEDIFF(), DATEPART(), FORMAT(), among others.

Examples

SELECT GETDATE();                          -- current date and time
SELECT SYSUTCDATETIME();                    -- current UTC date/time, more precise

SELECT DATEADD(DAY, 7, GETDATE());          -- 7 days from now
SELECT DATEADD(MONTH, -1, GETDATE());       -- 1 month ago

SELECT DATEDIFF(DAY, '2026-01-01', '2026-06-15');   -- 165 (days between the two dates)

SELECT DATEPART(YEAR, OrderDate) AS OrderYear,
       DATEPART(MONTH, OrderDate) AS OrderMonth
FROM Orders;

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');     -- formatted string, e.g. '2026-06-15'

-- Find orders from the last 30 days
SELECT * FROM Orders WHERE OrderDate >= DATEADD(DAY, -30, GETDATE());

Common mistake

Applying a function to a date column in a WHERE clause (e.g. WHERE YEAR(OrderDate) = 2026) — this prevents SQL Server from using an index on that column efficiently, since it must compute the function for every row before it can compare, forcing a full table scan.

-- Non-sargable -- can't use an index on OrderDate efficiently
SELECT * FROM Orders WHERE YEAR(OrderDate) = 2026;

-- Sargable -- compares the column directly, index-friendly
SELECT * FROM Orders
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01';

Quick practice

1. What does DATEADD(DAY, 7, GETDATE()) return?

   Answer

   The current date/time plus 7 days.

2. Why is WHERE YEAR(OrderDate) = 2026 generally worse for performance than a direct date-range comparison?

   Answer

   Wrapping the column in a function makes the condition "non-sargable" — SQL Server can't use an index efficiently, since it would need to evaluate the function on every row rather than seek directly using the index.

3. What does DATEDIFF(DAY, date1, date2) return?

   Answer

   The number of day boundaries crossed between date1 and date2, as an integer.