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 one 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!
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.
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:
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.
|Parts of this blog|
25 Aytoun Street