Arindam Mondal

SQL Subtract Dates with Examples

January 2, 2023 by

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’.

DATEADD for month result

The below DATEADD will add 1 year to the provided date value, the year changed from 2022 to 2023.

DATEADD for year result

This will add 1 day of the year to the provided date value, the date value changed from 30 to 31.

DATEADD for dayofyear result

Below DATEADD will add 1 day to the provided date value, the date value changed from 30 to 31.

DATEADD for day result

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:

SQL Subtract Dates for week

The below DATEADD will add 1 weekday to the provided date value, the date changed from 30 to 31.

DATEADD for weekday result

The below DATEADD will add 1 hour to the provided date value, the hour changed from 0 to 1.

DATEADD for hour result

The below DATEADD will add 1 minute to the provided date value, the date changed from 0 to 1.

DATEADD result for minute

In the below example, it will add 1 second to the provided date value, the date changed from 0 to 1:

DATEADD for second result

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:

another DATEADD for week result

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.”

DATEADD error for Arithmetic overflow

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.”

DATEADD error caused by overflow

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:

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:

Datediff result for year

The quarter datepart returns the quarterly difference between the below two dates:

Datediff result for quarter

The month datepart returns the monthly difference between the below two dates:

Datediff result for month

The dayofyear datepart returns the day difference between the below two dates:

Datediff result for dayofyear

The day datepart returns the day difference between the below two dates:

Datediff result for day

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:

Datediff result for week

The hour datepart returns the hourly difference between the below two dates:

Datediff result for hour

The minute datepart returns the minute difference between the below two dates:

Datediff result for minute

The second datepart returns the second difference between the below two dates:

Datediff result for second

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:

Datediff result for millisecond error

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:

Datediff result for millisecond

Similar to the above example for microsecond differences, we will use the DATEDIFF_BIG function:

Datediff result for microsecond error

For nanosecond differences also we will use the DATEDIFF_BIG function

Datediff result for nanosecond error

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.

Datediff result for week

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:

Datediff result for year

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:

Datediff result for hour

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 Mondal
Latest posts by Arindam Mondal (see all)
T-SQL

About Arindam Mondal

Arindam is an experienced and highly motivated IT enthusiast who likes to leverage his technical expertise to address critical business needs. A self-guided learner who loves to learn every single day. He loves to indulge in cultural diversity and travel to newer destinations. 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)

405 Views