BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Dates and times can be the most frustrating data types to work with in SQL Server. Learn everything you need to know about the way dates work with this handy blog series and enjoy happier times!
- Dates and Times in SQL
- Date and Time Data Types in SQL Server
- Dates and Times in Criteria
- Formatting Dates and Times in SQL Server
- Calculations with Dates in SQL Server (this blog)
- How to Calculate Age in SQL Server
Posted by Andrew Gould on 13 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Calculations with Dates in SQL Server
All calculations with dates in SQL Server rely on the fact that dates are really just numbers with a fancy format. In most cases you can get away with simple arithmetic to reach the answer you want, although there are a few useful functions that can help in certain situations.
Simple Date Calculations
The simplest sort of calculation you can perform with a date is to simply add a number to it:
Here we've added the DeliveryTime value to the OrderDate to work out what the DeliveryDate will be.
Working Out the Difference Between Dates
Of course, you can also subtract a number from a date to calculate past dates but, more usefully, you can subtract a date from another date to work out the difference in days between them.
Here we've subtracted today's date at the time of writing from a list of important dates (don't forget to wish us a happy birthday!).
Slightly oddly, when you subtract one date from another, you get a date as the answer. The solution is to convert the data type of the answer to one of the number data types.
Here we've used the CAST function, but you could also use CONVERT as described in the previous part of this blog series.
Calculating Today's Date
The solution above for calculating the difference between dates has one major flaw: if we run the query tomorrow it will give us the wrong answer. What we really need is a way to calculate what today's date is whenever the query runs. Fortunately SQL Server provides us with a way to do that in the form of the GETDATE function.
Using the GETDATE function means that the query will always return the correct answer, no matter which day we choose to run it.
You can also subtract a date from today's date to work out how many days have elapsed since an important date. If you want to give yourself a shock, try calculating how old you are in days!
Working with Different Units of Time
Using simple numbers we can add or subtract days to/from a date and we can work out the difference between two dates in terms of days, but what if we want to add a number of weeks to a date, or work out the difference in hours between two dates? In order to do this we need to use the DATEADD and DATEDIFF functions respectively.
The DATEADD function has the following basic syntax:
The diagram below shows a couple of examples of using the DATEADD function.
Here we're subtracting 3 hours and adding 4 weeks to the time of writing (isn't it sad that this is the most exciting thing I could think of doing on a Friday night?!)
The DATEDIFF function has the following basic syntax:
Some examples of using DATEDIFF are shown below:
Here we're calculating the difference between today and three important dates in hours, days and weeks.
The date and time codes for the two functions we've described above are the same as those for the DATENAME and DATEPART functions discussed in the previous part of this blog series.
You now know most of what there is to know about calculations with dates in SQL Server. All that remains is to use your imagination to put together the individual techniques you've learnt to calculate the answers you need. As one final helping hand, the concluding part of this blog series will teach you how to perform one of the most commonly required calculations in SQL: working out somebody's age from their date of birth.