SQL Server provides various dates and time functions for different needs. In this article, we will focus on SQL subtract date functions with various examples.
Introduction
SQL Server has numerous built-in SQL date and time data type which includes time, date, smalldatetime, DateTime, datetime2, and datetimeoffset. SQL Server also provides a wide range of built-in date and time functions so that you can play with your date and time values as per your application’s requirements. Higher-precision system date and time functions include SYSDATETIME, SYSDATETIMEOFFSET, and SYSUTCDATETIME. Lower-precision system date and time functions including CURRENT_TIMESTAMP, GETDATE, GETUTCDATE. Few other SQL functions return date and time parts for example DATE_BUCKET, DATENAME, DATEPART, DATETRUNC, DAY, MONTH, and YEAR. Some functions return date and time values from their parts DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS, TIMEFROMPARTS, DATEDIFFDATEDIFF_BIG.
In this article, we will focus on the two most popular DateTime functions DATEADD and DATEDIFF which are used to subtract dates and times in various formats.
DATEADD
Let’s discuss the first function in this article for SQL subtract dates. The DATEADD function adds a number to the date part value and returns the updated date or time value.
The syntax is DATEADD (datepart, number, date). It takes three inputs the datepart, number, and date. The first input is datepart, this is the portion of the date which you want to add to the integer number. The list includes the year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, and nanosecond, The second input is the numbers to add the date, if the number is a decimal fraction, then the number will truncate but it will not round the number value in this case, use a positive number to get future dates and use negative values to get past dates.
Let’s see a few examples of SQL subtract date from the DATEADD function:
In the below example, we are adding one month to the existing date ‘20220730’.
|
1 2 3 |
SELECT DATEADD (month, 1, '20220730'); |
The below DATEADD will add 1 year to the provided date value, the year changed from 2022 to 2023.
|
1 2 3 |
SELECT DATEADD (year,1,'2022-07-30') |
This will add 1 day of the year to the provided date value, the date value changed from 30 to 31.
|
1 2 3 |
SELECT DATEADD (dayofyear,1,'2022-07-30') |
Below DATEADD will add 1 day to the provided date value, the date value changed from 30 to 31.
|
1 2 3 |
SELECT DATEADD (day,1,'2022-07-30') |
There is no function available in SQL Server to subtract or reduce the given DateTime value. We need to use negative numbers in this case. In the below example, the DATEADD will subtract 1 week from the provided date value, the date time value is now reduced to 1 week:
|
1 2 3 |
SELECT DATEADD (week, -1,'2022-07-30') |
The below DATEADD will add 1 weekday to the provided date value, the date changed from 30 to 31.
|
1 2 3 |
SELECT DATEADD (weekday,1,'2022-07-30') |
The below DATEADD will add 1 hour to the provided date value, the hour changed from 0 to 1.
|
1 2 3 |
SELECT DATEADD (hour,1,'2022-07-30') |
The below DATEADD will add 1 minute to the provided date value, the date changed from 0 to 1.
|
1 2 3 |
SELECT DATEADD (minute,1,'2022-07-30') |
In the below example, it will add 1 second to the provided date value, the date changed from 0 to 1:
|
1 2 3 |
SELECT DATEADD(SECOND,1,'2022-07-30') |
As mentioned in the above examples you can use the DATEADD function in various ways for SQL to subtract dates value. You may get the same return value from dayofyear, day, and weekday. If it meets the conditions – datepart is month, the date month has more days than the return month and the date day should not be available in the return month. If all the above conditions are true. Then, DATEADD returns the last day of the return month. For example, September has 30 (thirty) days; therefore, the below statements return 2006-09-30 00:00:00.000:
|
1 2 3 4 |
SELECT DATEADD (month, 1, '20220830'); SELECT DATEADD (month, 1, '2022-08-31'); |
There is a limitation on the number argument, it cannot be exceeded the range of int, in the below example, it returns an error message ” Msg 8115, Level 16, State 2, Line 6 Arithmetic overflow error converting expression to data type int.”
|
1 2 3 4 |
SELECT DATEADD (year, 2147483648, '20060731'); SELECT DATEADD (year, -2147483649, '20060731'); |
These are the few constraints while working with SQL subtract dates. If the date argument is incremented to a value outside the range of its data type, then DATEADD returns an error. In the following example, the number value added to the date value exceeds the range of the date data type. It returns an error message “Msg 517, Level 16, State 1, Line 10 Adding a value to a ‘DateTime’ column caused an overflow.”
|
1 2 3 4 |
SELECT DATEADD (year, 2147483646, '20060731'); SELECT DATEADD (year, -2147483646, '20060731'); |
DATEDIFF
Now we will discuss another important function for SQL subtract dates. Let’s say now we want to compare two date values and get the differences between them. So, SQL Server provides the DATEDIFF function to do such tasks. This function returns the integer value of the provided datepart values crossed between the specified start date and end date. The syntax is below:
|
1 2 3 |
DATEDIFF (datepart, start date, end date) |
It takes three inputs, the first one is the datepart, it takes various datepart like a year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond, and nanosecond. The second input is the start date, and the third input is the end date.
Let’s see a few examples of the DATEDIFF function to learn SQL subtract dates. We are using startdate ‘2022-09-01 23:59:59.9999999’ and enddate ‘2023-10-02 00:00:00.0000000’
In the below example the year datepart returns the year difference between the specified dates:
|
1 2 3 |
SELECT DATEDIFF (year,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
The quarter datepart returns the quarterly difference between the below two dates:
|
1 2 3 |
SELECT DATEDIFF (quarter,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
The month datepart returns the monthly difference between the below two dates:
|
1 2 3 |
SELECT DATEDIFF (month,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
The dayofyear datepart returns the day difference between the below two dates:
|
1 2 3 |
SELECT DATEDIFF (dayofyear,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
The day datepart returns the day difference between the below two dates:
|
1 2 3 |
SELECT DATEDIFF (day,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
The week datepart returns the weekly difference between the below two dates, in this example, we reversed the start date and end date, now start date is bigger than the end date, so we received a negative value in the result:
|
1 2 3 |
SELECT DATEDIFF (week,'2023-10-02 00:00:00.0000000', '2022-09-01 23:59:59.9999999'); |
The hour datepart returns the hourly difference between the below two dates:
|
1 2 3 |
SELECT DATEDIFF (hour,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
The minute datepart returns the minute difference between the below two dates:
|
1 2 3 |
SELECT DATEDIFF (minute,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
The second datepart returns the second difference between the below two dates:
|
1 2 3 |
SELECT DATEDIFF (second,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
Now let’s see some exceptions to the DATEDIFF function to understand SQL subtract dates. The millisecond datepart returns the millisecond difference between the below two dates:
|
1 2 3 |
SELECT DATEDIFF (millisecond,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
If you see the above screenshot, we got an error message saying “The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart”. The datediff function is unable to handle the millisecond differences between the specified date range because it is too large. The datediff function returns value is int, if a return value out of range for int (-2,147,483,648 to +2,147,483,647), DATEDIFF returns an error. so, SQL Server provides another function DATEDIFF_BIG to handle larger date differences. In the below example we are using the DATEDIFF_BIG function instead of the DATEDIFF function:
|
1 2 3 |
SELECT DATEDIFF_BIG (millisecond,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
Similar to the above example for microsecond differences, we will use the DATEDIFF_BIG function:
|
1 2 3 |
SELECT DATEDIFF_BIG (microsecond,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
For nanosecond differences also we will use the DATEDIFF_BIG function
|
1 2 3 |
SELECT DATEDIFF_BIG (nanosecond,'2022-09-01 23:59:59.9999999', '2023-10-02 00:00:00.0000000'); |
There is an exception for SQL subtract dates for a millisecond, the maximum difference between the startdate and enddate is 24 days, 20 hours, 31 minutes, and 23.647 seconds. For a second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes, and 7 seconds.
If startdate and enddate are both assigned only a time value, and the datepart is not a time datepart (for example day or week), DATEDIFF returns 0.
|
1 2 3 |
SELECT DATEDIFF (WEEK,'00:00:00.0000000','23:59:59.9999999'); |
In the below query, the date part in the startdate is missing and the end date contains the full datetime value. Let’s execute the query:
|
1 2 3 |
SELECT DATEDIFF (Year,'00:00:00.0000000','2022-09-01 23:59:59.9999999'); |
Although we have not provided any date value in the start date option in the above example the DATEDIFF function returns a value which is 122 because the DATEDIFF sets the value of the missing date part to the default value: 1900-01-01.
similar to the above example DATEDIFF function also used the default time value ’00:00:00.0000000′ if the time part is not specified in the startdate or enddate. In the below query the time part in the startdate is missing, Let’s execute the query:
|
1 2 3 |
SELECT DATEDIFF (HOUR,'2022-09-02','2022-09-02 23:59:59.9999999'); |
As expected, the above query returns the difference value in an hour which is 22.
Conclusion
In this article, we discussed SQL subtract dates with various examples. SQL Server provides a wide range of date and time functions to play with date values as per your application’s requirement. We learned the two most popular DateTime functions DATEADD and DATEDIFF.
Arindam has rich experience in Azure solution implementation and support activities for large enterprise clients, having worked on multiple developments and enhancement projects. He is
currently based in India and is working as a Technical Lead for a leading MNC Company.
A few significant facts about his career:
* Has over 10.7 years of IT experience in Microsoft platforms.
* Has around 8 years of experience in SQL database development (T-SQL/Performance tuning/CDC) and ETL-SSIS.
* Has 5 years' experience in Azure Environment (Azure Data Factory, Azure Data Lake Storage, Azure SQL Database, Azure Cosmos DB, Azure Synapse Analytics)
- Understanding the SQL IN operator with examples - March 19, 2024
- An introduction to PostgreSQL Data Types with examples - September 15, 2023
- Understanding Substring in Oracle SQL - March 22, 2023