This page has 0 threads | Add post

Dates and Times in SQL

Written by Andrew Gould

In this tutorial

When is a Date not a Date?
So What?

Working with dates and times in SQL can sometimes be frustrating: even doing something as simple as changing the format of a date can be a challenge! This tutorial is designed to show you how dates work from the absolute basics through to being able to perform complex calculations with them.

When is a Date not a Date?

Answer: When it's a number!

Ok, that wasn't much of a joke, but it does have the virtue of being accurate. In SQL (and pretty much every other computer application) a date is really just a number with a fancy format. The query below formats a range of numbers as dates to demonstrate the principle:

The CAST function is used to change one data type into another.

From the example above you can see that the number 0 represents midnight on the 1st of January, 1900. One full 24 hour period has a value of 1, so the number 1 represents the following day, number 2 is the day after that and so on. Times are represented by the decimal component of a number, so the number 1.5 represents midday on the 2nd of January, 1900. Simple!

It stands to reason that if we can format a number as a date we can also do the opposite:

Three dates displayed as numbers.

From the example above you can see that the 1st of January 2000 is 36,524 days after the 1st of January 1900.

As a bit of date trivia, did you know that the earliest date you can store in a SQL Server database is the 1st of January 1753? It's not as arbitrary a date as it might first appear. In 1752 Great Britain and her colonies (including America) finally changed from the Julian to the Gregorian calendar. To avoid having to provide two different date systems for dates prior to this, SQL Server only allows you to store dates in the Gregorian system.

NB: A new data type was introduced in SQL Server 2008 allowing you to store dates as early as the 1st of January 1 AD. We'll talk more about data types in the next part of this series.

So What?

You might be thinking that this isn't particularly useful information (other than as a conversation starter at a party), but you'd be wrong! Knowing that dates are simply numbers opens up all sorts of possibilities in terms of what we can calculate:

You can calculate the age of someone or something by working out the difference between any two dates.
You could calculate a completion time for a task or delivery date for an order by adding a duration to a start time.
You can create a countdown timer to an important event by subtracting today's date from a future date.

All of the calculations mentioned above are based on the principle that a date is just a number. We'll show you how to create these types of calculations later on in this series of blogs, but first we'll look at the different data types you can use for dates in SQL Server.

Previous
tutorial

Calculations and Expressions

Previous
part

Top of page

Next
part

Date and Time Data Types in SQL Server

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

Dates and Times in SQL

When is a Date not a Date?

So What?

Head office

London

Manchester