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

This blog is part of a larger tutorial on SQL Server, which you can read here. Wise Owl also run SQL courses, as well as training in SSRS, SSAS and SSIS.

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:

**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 dat**

**e, 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 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.

- 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