This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! 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 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.

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!

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

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

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.

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.

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Dates and Times in SQL

When is a Date not a Date?

So What?

Head office

London

Manchester