SQL DateAdd Function: Use Cases & Examples

From tracking project timelines to analyzing sales performance, the ability to work with date and time data is crucial.

You may know today’s date, but what if you wanted to know the date three weeks from now or what it was exactly one year ago?

This is where the SQL DateAdd function comes into play.

SQL’s DateAdd function allows you to add or subtract a specified time interval from a date or datetime value. By specifying the unit of time (e.g., day, month, year) and the number to add or subtract, you can easily calculate new dates.

This function is particularly useful in database queries where date calculations are essential, such as forecasting, aging, or scheduling.

In this guide, we’ll delve deep into the syntax, usage, and practical applications of the SQL DateAdd function, setting you up to harness its full potential in your data management tasks.

Let’s get started!

Overview and Syntax of the SQL DateAdd Function

The DateAdd function in SQL is a powerful tool for modifying dates and times. The function is supported by various SQL implementations, primarily in SQL Server, making it an integral part of any SQL programmer’s toolbox.

The main purpose of using the DateAdd function is to perform calculations with dates, such as finding the start or end date of a period, calculating the difference between two dates, or determining the date a specific number of days, months, or years in the future or past.

To use the DateAdd function, you need to provide three arguments:

• Datepart: This represents which part of the date you would like to modify; this could be year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond, or nanosecond, or any other valid date part.
• Number: This is an integer number specifying how much you want to add or subtract.
• Date. The input date is to be modified.

The syntax for using the SQL DateAdd function is as follows:

DATEADD(datepart, number, date)

An example of using the DateAdd function in SQL Server to add two months to a specific date would look like this:

SELECT DATEADD(month, 2, '2023/07/20') AS ModifiedDate;

In this example, we specified ‘month’ as the date part, 2 as the number, and ‘2023/07/20’ as the input date.

As a result, the ModifiedDate column returned by the query would show ‘2023/09/20’, indicating that two months have been added to the input date.

The DateAdd function is versatile and you can combine it with other SQL functions, conditions, and queries to perform more complex date calculations and manipulations.

It’s also widely supported across different SQL platforms, making it an essential function for all SQL developers to learn and understand.

How to Use DateAdd with Different Data Types

When using the SQL DateAdd function, you should be aware of the various data types that can be used as input and returned as output.

The function accepts many data types as input, including DATE, DATETIME, DATETIME2, DATETIMEOFFSET, SMALLDATETIME, and TIME.

Let’s dive into more detail about these data types, along with a few others that you might find useful when working with dates and times.

1. DATETIME

The DATETIME data type in SQL Server stores both date and time information. The date range for DATETIME is from January 1, 1753, through December 31, 9999, with an accuracy of .00333 seconds, or approximately 3.33 milliseconds.

When using DATETIME with DateAdd, it’s important to note that adding or subtracting a fraction of a day will modify the time portion of the DATETIME:

SELECT DATEADD(DAY, 0.5, '2023-08-05T00:00:00') AS NewDatetime

This will return ‘2023-08-05 00:00:00’, because we’ve added half a day to the original datetime.

2. DATETIME2

DATETIME2 is an extension of the DATETIME type that has a larger date range, larger default fractional precision, and optional user-specified precision. It can store dates from January 1, 0001, through December 31, 9999, with an accuracy of 100 nanoseconds.

DATETIME2 can be particularly useful when you need to store dates outside the DATETIME range or require greater fractional seconds precision.

Like DATETIME, DATETIME2 can also be used with DateAdd and fractional days or decimal value numbers will affect the time portion:

SELECT DATEADD(DAY, 0.1, '2023-08-05T00:00:00') AS NewDatetime2

This will return ‘2023-08-05 02:24:00.0000000’ because we’ve added a tenth of a day to the original datetime2.

3. Other Date and Time Data Types

There are several other date and time data types you might find useful:

• DATE: Stores a date without a time. The range is from January 1, 0001, through December 31, 9999.
• TIME: Stores a time without a date. The range is 00:00:00.0000000 through 23:59:59.9999999.
• SMALLDATETIME: Similar to DATETIME, but with less precision. It stores dates and times of day with less fractional seconds precision and a smaller range of “acceptable” dates.
• DATETIMEOFFSET: This type is used to store a date that includes the time of day with a time-zone-aware time offset. This can be particularly useful for applications that span multiple time zones.

TIME data type stores only the time component, so you can use DateAdd to add time units like hours, minutes, or seconds to a TIME value. The result will still be a TIME data type.

One notable data type that can’t be used with DateAdd is TIMESTAMP, which is a distinct data type used only for version control and row updates in SQL Server.

When working with NULL values, using DateAdd will return a NULL result. This means that if either the input date or the number of time units is NULL, the function will return NULL.

We’ve covered a lot of ground so far, so in the next section, let’s look at some practical examples that show you how to actually use the DateAdd function.

13 Examples of DateAdd Function in Use

The practical utility of the DateAdd function becomes evident when applied to real-world scenarios. Through examples, we can better understand its versatility in manipulating date and datetime values for various applications.

Here are some practical examples of how this function can be used:

1. Adding Days to a Date

You can use the DateAdd function to add a certain number of days to a date.

For example, if you want to find out what the date will be 10 days from August 5, 2023, you would use:

2. Subtracting Days from a Date

Similarly, you can subtract a certain number of days from a date.

To find out the date 15 days before August 5, 2023, you would use:

3. Adding Months to a Date

The DateAdd function can also be used to add months to a date.

For example, to find out the date six months after August 5, 2023, you would use:

4. Subtracting Months from a Date

To find out the date six months before August 5, 2023, you would use a negative value in the number variable:

5. Adding Years to a Date

You can add years to a date with the DateAdd function.

To find out the date five years after August 5, 2023, you would use the following example:

6. Subtracting Years from a Date

To find out the date five years before August 5, 2023, you would use the following query:

7. Adding Hours, Minutes, or Seconds to a DateTime

The DateAdd function can also be used with multiple datetime values.

For example, to add 2 hours, 30 minutes, and 45 seconds to August 5, 2023, 10:00:00, you would input:

In the above example, we added specific number argument values to each argument. The result is August 5, 2023, 12:30:45 or 2023-08-05 12:30:45.

8. Subtracting Hours, Minutes, or Seconds from a DateTime

Similarly, you can subtract time from a datetime.

To subtract 2 hours, 30 minutes, and 45 seconds from the datetime we used in the previous example, you would type:

This time the result is 2023-08-05 07:29:15.

9. Calculating the End of the Month

You can use DateAdd to calculate the last day of the current month, shown in the following query:

This query first adds one month to the current date (August 5), then subtracts one day to give September 4, 2023.

10. Calculating the Start of the Month

Similarly, you can calculate the first day of the current month:

This query uses DATEDIFF function to find the number of months between the current date and ‘1900-01-01’, then adds that many months to ‘1900-01-01’, effectively rounding down to the start of the current month.

11. Calculating Age

You can use SQL Case, DateAdd, and DATEDIFF together to calculate a person’s age based on their birthday:

SELECT 
    CASE
        WHEN DATEADD(YEAR, DATEDIFF(YEAR, '1980-04-25', GETDATE()), '1980-04-25') > GETDATE() 
        THEN DATEDIFF(YEAR, '1980-04-25', GETDATE()) - 1
        ELSE DATEDIFF(YEAR, '1980-04-25', GETDATE())
    END AS Age

In this query, we use DATEDIFF to calculate the number of years between the current date and the birthday (April 25, 1980). We then add these years to the birthday using DateAdd.

If the resulting date is greater than the current date, it means the birthday hasn’t occurred yet this year, so we subtract 1 from the age. Otherwise, we just use the number of years as the age.

The result is 43, which would be the age in 2023 of a person born in April 1980.

12. Adjusting Dates by Business Quarters

You can adjust dates by business quarters using DateAdd. For example, to get the date one business quarter (3 months) from now:

And to get the date one business quarter ago:

13. Adding Milliseconds or Nanoseconds to a DateTime

With DATETIME2 data types, you can add or subtract time intervals as small as milliseconds or nanoseconds.

For example, to add 500 milliseconds to a datetime:

SELECT DATEADD(MILLISECOND, 500, '2023-08-05T10:00:00') AS NewDatetime

Handling Errors When Using SQL DateAdd

When working with the DateAdd function in SQL, it’s crucial to anticipate and manage potential errors that might arise. Incorrect usage, boundary conditions, or unexpected input values can lead to undesired results or even halt the execution of your SQL statements.

In this section, we’ll go over common errors associated with the DateAdd function and provide strategies to handle and prevent them effectively.

1. Incorrect Datepart Value

The first argument that DateAdd takes is the part of the date that you want to add to or subtract from (e.g., year, month, day, hour, minute, second, millisecond). This is called the datepart, and if you provide an unrecognized or unsupported datepart value, you’ll get an error.

For example, SQL Server doesn’t support nanosecond precision, so using ‘NANOSECOND’ as the datepart will cause an error message. The smallest unit you can use is ‘MILLISECOND’. You can always use decimal fraction values to convert the millisecond into nanosecond.

Solution: Always make sure to use a supported datepart value. Consult the SQL Server documentation for a list of valid datepart values.

2. Non-Numeric Interval Value

The second argument to DateAdd is the number of intervals to add or subtract. This must be a numeric value. If you provide a non-numeric value, you’ll get an error.

Solution: Always ensure the interval value is a number. If you’re using a variable for this argument, check its value before using it in DateAdd.

3. Invalid or Out-of-Range Date

The third argument to DateAdd is the date that you want to add to or subtract from. If this date is not a valid date or datetime value, or if the result of the DateAdd operation would result in a date outside the valid range (year 0001 to 9999), you’ll get an error.

Solution: Always ensure the date value is valid and within range. If you’re adding or subtracting large intervals, consider checking whether the operation will result in an out-of-range date before executing it.

4. Incorrect Data Type

If the date argument is not of a date or datetime data type, SQL Server will try to convert it to a datetime value. If this conversion is not possible, you’ll get an error.

Solution: Always ensure the date argument is of the correct data type. If necessary, explicitly convert it to a date or datetime value using the CAST or CONVERT function.

Final Thoughts

The examples we’ve covered illustrate just a fraction of what’s possible with DateAdd. Whether you’re grouping transactions by quarter, calculating prior year dates, or predicting future dates, the DateAdd function provides a straightforward solution.

However, remember that SQL is a powerful language with many functions to handle dates and times. While DateAdd is incredibly useful, it’s only one piece of the puzzle.

Make sure to explore a combination of other date and time functions like DATEDIFF, GETDATE, and CONVERT to fully harness the power of SQL in dealing with date and time data.

If you’d like to learn how to break down table lists and build a model around dates and different values, check out our Power BI tutorial below:

Frequently Asked Questions

Is date a string in SQL?

No, date is not a string in SQL. It is a data type that stores dates. However, SQL provides functions like CONVERT(), CAST(), and PARSE() to convert a string to a date.

Similarly, you can convert a date to a string using the same functions. The ISDATE() function can be used to check if a string is a valid date.

How to set date format in SQL?

In SQL, the date format can be set using SET DATEFORMAT command or by using the CONVERT() or FORMAT() functions.

SET DATEFORMAT command overrides the implicit date format setting of the language. The syntax is as follows:

SET DATEFORMAT format_order;

Where format_order can be mdy, dmy, ymd, ydm, myd, or dym.

How to convert a column into a date in Structured Query Language?

To convert a column into a date in SQL, you can use the CAST(), CONVERT(), or TO_DATE() functions depending on your SQL server.

Here’s an example using CAST() function:

SELECT CAST(your_column AS DATE) FROM your_table;