This page has 0 threads | Add post

Calculations with Dates in SQL Server

Written by Andrew Gould

In this tutorial

Simple Date Calculations
Working Out the Difference Between Dates
Calculating Today's Date
Working with Different Units of Time
What's Next?

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:

DATEADD(Unit of time, Number of units to add, Date to start with)

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:

DATEDIFF(Unit of time, Start date, End date)

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.

What's Next?

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 parts of this tuitorial 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.

Previous
tutorial

Calculations and Expressions

Previous
part

Formatting Dates and Times in SQL Server

Top of page

Next
part

How to Calculate Age in SQL

Next
tutorial

Joins

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Dates and times

Calculations with Dates in SQL Server

Simple Date Calculations

Working Out the Difference Between Dates

Calculating Today's Date

Working with Different Units of Time

What's Next?

Head office

London

Manchester