564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Dates and Times in SQL Server
Part five of a six-part series of blogs
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!
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.
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.
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.
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!
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.
|Parts of this blog|
25 Aytoun Street