How To Use The SQL Server DATEADD Function

Introduction

In this follow up to the tutorial on using the SQL DATEADD function in MySQL, we will show you how to add up dates using the SQL Server version of this date adding function. So what exactly does the SQL Server DATEADD() function do? It adds or subtracts a specified time interval from a specified date. Dates can be complex data structures as they are formed from several different components which include milliseconds, seconds, minutes, hours, days, weeks, months, years and so on. Not only do they hold the above elements but they also go further to describe phrases like month of the year and day of the week. There is also the matter of identifying leap years, twenty four hour time verses AM or PM.

Trying to keep track of all of this may be daunting to the average user, so SQL Server has made things a little easier for you to understand.

Syntax of the SQL Dateadd Function

The Syntax of the SQL Server DATEADD function is pretty simple. The illustration below shows how this function should be used.

DATEADD( datepart, number, date )

Where number is the amount of units you want to add which can either be positive, for dates in the future, or negative, for dates in the past. A zero has no effect. Date is a valid date expression.

Expressions for the datepart can be one of the following:

year “yy, yyyy”

quarter “qq, q”

month “mm, m”

dayofyear “dy, y”

day “dd, d”

week “wk, ww”

weekday “dw, w”

hour “hh”

minute “mi, n”

second “ss, s”

millisecond “ms”

microsecond “mcs”

nanosecond “ns”

Alternates for the datepart expressions are shown in quotes besides each expression. These can be used interchangeably.

Examples Showing How To Use DATE_ADD

Assuming you had a table with several invoices listed. Each invoice is meant to be due 21 days after they have been drawn up. To get the due dates of the invoices, we would use the DATEADD function to do this for us automatically.

In our invoice table we may have two columns. One being InvoiceID and the other being InvoiceDate. The date in the InvoiceDate column may be a simple date as follows.

2010-10-01 15:12:09

Here is how we would write our SQL query.

SELECT InvoiceId,DATEADD(day, 21, InvoiceDate) AS InvoiceDueDate FROM Invoices

The result for the InvoiceDueDate would be.

2010-10-22 15:12:09

Using the same date above, If I predict a task will run for two hours longer than expected then I would do something like this to get the new end time.

SELECT DATEADD(hour, 2, ‘2010-10-01 15:12:09’)

The result of the above SQL query would be.

2010-10-01 17:12:09

Using the SQL DATEADD functions you can also be able to add up dates in reverse order. In short you can be able to subtract elements from a datetime object. Supposing I know a task is supposed to be completed by certain day and I know that particular task is going to take at most 7 days, with the DATEADD function you can set the latest date by which the task should have started.

Here is an example.

SELECT DATEADD(day, -7, ‘2010-10-21 15:12:09’)

The result of the above SQL query would be.

2010-10-14 15:12:09

Conclusion

Judging from the above examples you can see how simple the SQL Server DATEADD function is to use. It encapsulates the complexities of adding and subtracting dates and lets the user focus on being more productive. Regardless of whichever datepart you use, the usage of this function is the same.

Be sure to read the tutorial on how to use the SQL DATEADD Function its Syntax & Examples using MySQL.