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