The Most Common Date Functions in T-SQL

Working with dates and times is a fundamental aspect of database development and reporting. T-SQL (Transact-SQL), Microsoft’s extension of SQL, offers a robust set of functions to handle date and time values efficiently. This article presents the most commonly used date functions in T-SQL, organized by category and use case, with syntax, examples, and notes on performance.

1. GETDATE() and SYSDATETIME()

Returns the current system date and time.

SELECT GETDATE();        -- e.g., 2025-05-01 14:32:45.340
SELECT SYSDATETIME();    -- e.g., 2025-05-01 14:32:45.3401234

Use SYSDATETIME() when you need higher precision.

2. DATEPART(), DATENAME(), YEAR(), MONTH(), DAY()

These functions extract components of a date.

SELECT DATEPART(weekday, GETDATE()); -- e.g., 5 (Friday)
SELECT DATENAME(weekday, GETDATE()); -- e.g., 'Friday'
SELECT YEAR(GETDATE());              -- e.g., 2025

3. DATEADD()

Adds (or subtracts) a time interval to a date.

-- Add 10 days
SELECT DATEADD(day, 10, '2025-05-01');  -- 2025-05-11

-- Subtract 1 month
SELECT DATEADD(month, -1, '2025-05-01'); -- 2025-04-01

This is critical for rolling time windows, future projections, etc.

4. DATEDIFF()

Calculates the difference between two dates, in terms of a specified date part.

-- Days between dates
SELECT DATEDIFF(day, '2025-01-01', '2025-05-01'); -- 120

-- Months between dates
SELECT DATEDIFF(month, '2024-05-01', '2025-05-01'); -- 12

Returns whole number differences. Always be mindful of truncation behavior.

EOMONTH()

Returns the last day of the month that contains the specified date.

SELECT EOMONTH('2025-05-01');        -- 2025-05-31
SELECT EOMONTH('2025-05-01', 1);     -- 2025-06-30

Perfect for financial or monthly reporting.

6. SWITCHOFFSET() and TODATETIMEOFFSET()

Used when working with time zones and datetimeoffset data types.

-- Adjust to UTC+2
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:00');

-- Set an offset explicitly
SELECT TODATETIMEOFFSET(SYSDATETIME(), '+03:00');

These functions are crucial for global applications with multiple time zones.

7. CONVERT() and CAST() (For Formatting and Type Conversion)

-- Convert to date only
SELECT CONVERT(date, GETDATE());      -- 2025-05-01

-- Convert to string
SELECT CONVERT(varchar, GETDATE(), 120); -- '2025-05-01 14:35:00'

📌 Use CAST() for ANSI SQL compatibility, and CONVERT() when style formatting is needed.

Bonus: ISDATE()

Checks if a string can be converted to a valid date.

SELECT ISDATE('2025-05-01');     -- 1 (true)
SELECT ISDATE('not-a-date');     -- 0 (false)

Great for data validation in dynamic T-SQL scripts.

Summary Table

Final Thoughts

Mastering these T-SQL date functions can save you countless hours when dealing with reporting, data transformations, and analytics. Whether you’re building a monthly dashboard, cleaning up dirty data, or optimizing for time-based queries, these tools form the backbone of time manipulation in SQL Server.