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 (this blog)
- 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
- 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.
Dates and Times in SQL
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 blog 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.
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.