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 (this blog)
- 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.
Date and Time Data Types in SQL Server
When you design a table to store dates and times in a SQL Server database you have a lot of choice in terms of data types to use.
There are six different date and time data types in SQL Server 2008 R2. Which one do you choose?
This part of this blog series explains what you can store with each data type, followed by a short section on how to enter dates into a table.
Date and Time Data Types
The table below gives you a brief summary of the date and time data types in SQL Server, although you can see a much more comprehensive description of each type here.
|This is the standard choice for dates and times in SQL Server databases. It's compatible with older versions of SQL Server and has a large enough range and accuracy for most uses.|
|New to SQL Server 2008, this data type gives a larger range and more accurate measurement of time.|
|Also new to SQL Server 2008, this allows you to store a date without a time component.|
|New in SQL Server 2008, this allows you to store a time without a date.|
|A shorter range of dates with an accuracy of only one minute. This requires less memory to store than full DATETIME values.|
|New in SQL Server 2008, this has the same range and accuracy as DATETIME2 but allows you to specify a modifier for world time zones.|
In practice we tend to find that we exclusively use the DATETIME data type for backwards compatibility with older versions of SQL Server.
Entering Dates and Times into a Table
As well as having a large number of date and time data types, SQL Server allows a huge variety of formats for entering date and time values in a table. There are far too many to list in a short blog like this so we're only going to mention a few specific examples, but for a comprehensive overview you might be interested in this page.
Most people will be familiar with entering dates in Microsoft Excel or Access in the standard "DD/MM/YYYY" format (or "MM/DD/YYYY" for our cousins across the pond). Fortunately, you can enter your dates in this same format in a table in SQL Server. Whichever format you enter your dates in, the format will change to the one shown in the diagram below.
|Enter a date in this standard format...||....and it will be converted into another format altogether.|
It might seem a bit strange to have your dates in a "YYYY-MM-DD" format including a time component even if you didn't enter one, but there is a good reason for it. The format shown above is part of an international standard known as ISO 8601 and is used to avoid confusion between different formats for dates in different countries. We think it's a good idea to try to always use this format when working with dates in SQL Server.
You might think that you don't really need to know this if SQL Server happily converts your dates automatically into the appropriate format, but beware! Not every part of SQL Server behaves in the same way when it comes to dates, as we'll see when we look at how to write criteria involving dates.