In this comprehensive article, I’ll walk you through everything you need to know about manipulating dates in SQL Server – from basic operations to advanced techniques. Let’s dive in.
SQL Server Date Math
Before we explore the specifics, it’s essential to understand why date manipulation is so critical in database applications:
- Business Intelligence: Accurate period-over-period comparisons drive decision making
- Compliance: Many industries (healthcare, finance, etc.) have strict time-based record-keeping requirements
- Performance: Properly formatted date operations can dramatically improve query speed
- Reporting: Almost every business report includes some form of date-based calculation
Check out SQL Server Date Data Types
Getting the Current Date and Time in SQL Server
Let’s start with the basics – retrieving the current date and time:
Example 1
We can execute the following query to return the date and time with SQL Server’s default precision.
-- Returns date and time with SQL Server's default precision
SELECT GETDATE() AS CurrentDateTime;After executing the above query, I obtained the expected output, as shown in the screenshot below.
Example 2
We can execute the following query to return the date and time with higher precision.
SELECT SYSDATETIME() AS CurrentDateTimeHighPrecision;After executing the above query, I obtained the expected output, as shown in the screenshot below.
Example 3
We can execute the following query to return just the current date.
SELECT CONVERT(date, GETDATE()) AS CurrentDate;Below is the expected output after executing the above query.
Example 4
We can execute the following query to return just the current time.
SELECT CONVERT(time, GETDATE()) AS CurrentTime;Below is the expected result after executing the above query.
Essential Date Math Operations in SQL Server
Now let’s explore the fundamental date math operations you’ll use regularly.
Adding and Subtracting Time Periods with DATEADD
The DATEADD Function is your primary function for adding or subtracting time intervals:
Example 1
Let us execute the following query to add 7 days to the current date.
SELECT DATEADD(day, 7, GETDATE()) AS OneWeekFromNow;I got the expected output after executing the above query.
Example 2
We can execute the following query to subtract 3 months from the current date.
SELECT DATEADD(month, -3, GETDATE()) AS ThreeMonthsAgo;Below is the expected result after executing the above query.
Example 3
We can execute the following query to add 4 hours to the current time.
SELECT DATEADD(hour, 4, GETDATE()) AS FourHoursLater;I obtained the expected output after executing the query as shown in the screenshot below.
Example 4
We can execute the following query to add 30 seconds.
SELECT DATEADD(second, 30, GETDATE()) AS ThirtySecondsLater;Below is the expected result after executing the above query.
DATEADD accepts the following interval types:
- year, yyyy, yy
- quarter, qq, q
- month, mm, m
- dayofyear, dy, y
- day, dd, d
- week, ww, wk
- hour, hh
- minute, mi, n
- second, ss, s
- millisecond, ms
- microsecond, mcs
- nanosecond, ns
Calculating Time Differences with DATEDIFF
The DATEDIFF Function calculates the difference between two dates:
Example 1
Let us execute the following query to find the days between two dates.
SELECT DATEDIFF(day, '2025-01-01', '2025-12-31') AS DaysInYear;Below is the expected result after executing the above query.
Example 2
Let us execute the following query to find the months between two dates.
SELECT DATEDIFF(month, '2024-03-15', GETDATE()) AS MonthsSinceDate;Below is the expected result after executing the above query.
Example 3
Let us execute the following query to find the minutes between two times.
SELECT DATEDIFF(minute, '2025-05-04 09:30:00', '2025-05-04 11:45:00') AS MinutesDifference;Below is the expected result after executing the above query.
Extracting Date Parts with DATEPART
To extract specific components from a date with DATEPART function:
Example 1
We can execute the following query to get the current month number (1-12).
SELECT DATEPART(month, GETDATE()) AS CurrentMonth;After executing the above query, I got the expected output as per the screenshot below.
Example 2
We can execute the query below to retrieve the day of the week (1 = Sunday, 2 = Monday, etc.).
SELECT DATEPART(weekday, GETDATE()) AS DayOfWeek;I got the expected output after executing the above query.
Example 3
We can execute the below query to get the hour from a datetime.
SELECT DATEPART(hour, GETDATE()) AS CurrentHour;I got the expected output after executing the above query as per the below screenshot.
Performance Considerations
Below are the performance tips
- Avoid functions on indexed columns– Bad (can’t use index) WHERE YEAR(OrderDate) = 2023 — Good (can use index) WHERE OrderDate >= ‘2023-01-01’ AND OrderDate < ‘2024-01-01’
2. Use appropriate data types– More efficient when you only need a date OrderDate date NOT NULL.
Conclusion
Date manipulation is so critical in database applications that you have to know as a database developer working with SQL Server. You can use the information mentioned in this article.
You may also like the following articles.
- Add Month To Date SQL Server
- SQL Server Date Format 101
- How to get the week number from a date in SQL Server
- How To Convert Date To String In SQL Server
- The datepart hour is not supported by the date function dateadd for the data type date.
- SQL Server Date Formatting
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.