Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andrew Gould
In this tutorial
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.
Which one should 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.
Data Type | Range | Description |
---|---|---|
DATETIME | 1753-01-01 00:00:00.000 | 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. |
DATETIME2 | 0001-01-01 00:00:00.0000000 | This data type gives a larger range and more accurate measurement of time. |
DATE | 0001-01-01 | This allows you to store a date without a time component. |
TIME | 00:00:00.0000000 | This allows you to store a time without a date. |
SMALLDATETIME | 1900-01-01 00:00 | A shorter range of dates with an accuracy of only one minute. This requires less memory to store than full DATETIME values. |
DATETIMEOFFSET | 0001-01-01 00:00:00.0000000 | 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 tutorial 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.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.