Microsoft SQL Server has several built-in system functions to help you calculate date information very easily. One of those helpful functions is called DATEADD.
In this very brief tutorial, we’ll discuss everything you need to know about the DATEADD system function and give you examples of how to use it. We’ll discuss these topics:
- The syntax of the DATEADD function
- The ‘datepart’ values you can use in the DATEADD function
- Examples of using the DATEADD function
- Tips, tricks, and links
Let’s get into it.
1. The syntax of the DATEADD function
The syntax for the DATEADD system function is very simple. It basically follows this layout:
DATEADD(<datepart>, <number>, <date>)
Let’s talk about each part of this layout:
- datepart: This is a keyword representing what kind of date interval you want use in the addition. For example, if you want to add one week to the date 12/25/2021, you would specify a datepart of “week“. If you wanted to add one day instead, the datepart would be the word “day“. There are many datepart values you can specify (week, day, month, hour, second, etc.). I will outline all possible keywords you can use as the datepart in the next section.
- number: This will be an integer representing how many dateparts you want to add to the date. Going back to our example, if we want to add one week to the date 12/25/2021, you would specify a number of “1“. If you wanted to add two weeks, the number would be “2“. So on and so forth!
- date: This value represents the starting date for which we want to perform addition against. Going back to our example, if you want to add one week to the date 12/25/2021, the date value would be ’12/25/2021′. If instead you wanted to add one week to the date 4/1/2021, the date value would be ‘4/1/2021’. If you specify a string literal date, it needs to be wrapped around single quotes like I have done.
The return value of the DATEADD function is, of course, the resulting date of the calculation. For example, if we want to use DATEADD to add one week to the date 12/25/2021, the return value should be 1/1/2022.
2. The ‘datepart’ values you can use in the DATEADD function
As I mentioned earlier, there are many different datepart values you can use in the DATEADD function. Here is the full list, pulled directly from the Microsoft documentation:
datepart      Abbreviation
year          yy or yyyy
quarter       qq or q
month        mm or m
dayofyear    dy or y
day           dd or d
week          wk or ww
weekday      dw or w
hour          hh
minute       mi or n
second       ss or s
millisecond  ms
microsecond  mcs
nanosecond  ns
As you can see, many of the datepart values can be abbreviated to something smaller. In my opinion, it’s better to just outline the whole word rather than an abbreviation. Some of the abbreviations aren’t very intuitive. For example, someone looking at your code might not understand the abbreviation ‘n‘ is for minute.
I think outlining the whole word simply makes your code more readable and intuitive, for yourself and others!
3. Examples of using the DATEADD function
Let’s go through some quick examples of using the DATEADD function. First, I’ll demonstrate the output when you outline a string literal value as your date value. Let’s calculate the date that is one week after Christmas:
That was easy enough. What about 2 weeks after Christmas? We just need to change the number in the middle to 2:
That was easy.
Now let’s use the DATEADD function as part of the SELECT list of a query. Take a look at this query that displays Order details for our woodworking business:
Let’s say we want to check in with each customer after they have been using the product for 6 months . We want to know if the product is holding up well, and ask the customer to leave a review of the product.
Using DATEADD, it’s very easy to calculate what date is 6 months after each OrderDate:
Easy peasy. Notice I chose to use an abbreviation for the datepart. Again, in my opinion, the letters ‘MM‘ aren’t as intuitive as the word ‘MONTH‘. What do you think? Leave a comment!
Next, I’ll demonstrate how you can use an expression as your date value. Let’s use the GETDATE() function within our DATEADD function to tell us what time it will be 8 hours from now. The GETDATE() functions returns the current date and time. I’m writing this post on December 29, 2021 at 7:27 AM):
Finally, I’ll demonstrate how you could use a variable as your date value:
4. Tips, tricks, and links
Here are some helpful tips you should know when working with the DATEADD system function:
Tip # 1: The data type of the output value depends on data type of your “date” value
Let’s take a look at that last SELECT statement:
The date value we’re passing to DATEADD is the variable @maxDateValue, which is a DATETIME data type. This means the data type of the resulting value will also be DATETIME. We can use the handy SQL_VARIANT_PROPERTY function to prove it:
If we change our variable to a DATETIME2 data type instead, we see that the resulting data type is DATETIME2:
So again, the data type of your output value will be the same as the data type of your input value. If you are using a string literal date value as your input, the resulting value will be a DATETIME data type:
Tip # 2: Your “number” value can be negative
If you want to calculate a past date, you can still use DATEADD to do it. You just need to specify a negative number in the function. For example, if you want to calculate the date 20 days ago, we can do that by specifying a number value of -20 (I’m writing this on 12/30/2021):
That checks out.
Tip # 3: If you use a string literal as your “date” value, you should outline the year in four-digit format
You might have noticed that when I specify a string literal date value, I outline the year in four-digit format:
If you outline a 2-digit year format, SQL Server might get confused. For example, if you specify a date of ’12/25/01′, does that mean Christmas of 2001 or of 1901? (or maybe 1801? 2401?).
Help SQL Server understand what you want by just outlining all four digits of the year!
Links
Here is a link to the official documentation for the DATEADD system function for further reading:
Next Steps:
Leave a comment if you found this tutorial helpful!
If you found this tutorial helpful, you might also benefit from understanding the DATENAME system function. This is another helpful system function we can use when working with dates in SQL Server. Make sure you check it out:
DATENAME function: Explained
Also, the DATETIME2 data type is a new, common data type offered by Microsoft SQL Server. This data type offers many great benefits, making it a great choice for storing date values in a database. You can find the full tutorial here:
DATETIME2 Data Type: Explained
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
I hope you found this tutorial helpful. If you have any questions, leave a comment. Or better yet, send me an email!
