T-SQL 101: 57 Adding and subtracting date and time periods using DATEADD in SQL Server T-SQL

When you’re working with T-SQL, you’ll often need to add time periods onto a date, perhaps onto today’s date. You might be adding (or subtracting) days, or hours, or minutes, or months, and more.

The DATEADD function is the one that we can use add or subtract intervals to the date and time. Now in the example shown here, I’ve started with 20190228, or 28th February 2019. I want to add on 12 days:

and you can see that the outcome is the 12th March 2019 as expected.

Now there are a wide variety of intervals that can be used, not just days. These are the available intervals:

These range from years right down to nanoseconds. You can also see that there are abbreviations for each of the intervals.

In strongly suggest that you do not use these abbreviations. You don’t want a situation where you see say, M, and are wondering if that’s a month, or a minute, or a millisecond.

You should always aim for readable code. It’s much, much better if you actually use the full word. So even words like nanosecond or millisecond, if you ever work with those, put the complete interval as part of the function instead of using the abbreviated code.

You’ll see lots of old programming where people have used the abbreviations; don’t follow that, and show them a better way of writing the code!

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2020-02-17