This page has 0 threads | Add post

Date and Time Data Types in SQL Server

Written by Andrew Gould

In this tutorial

Date and Time Data Types
Entering Dates and Times into a Table

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.

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.

Data Type	Range	Description
DATETIME	1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997	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 to 9999-12-31 23:59:59.9999999	This data type gives a larger range and more accurate measurement of time.
DATE	0001-01-01 to 9999-12-31	This allows you to store a date without a time component.
TIME	00:00:00.0000000 to 23:59:59.9999999	This allows you to store a time without a date.
SMALLDATETIME	1900-01-01 00:00 to 2079-06-06 23:59	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 to 9999-12-31 23:59:59.9999999	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 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.

Previous
tutorial

Calculations and Expressions

Previous
part

Dates and Times in SQL

Top of page

Next
part

Dates and Times in Criteria

Next
tutorial

Joins

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Dates and times

Date and Time Data Types in SQL Server

Date and Time Data Types

Entering Dates and Times into a Table

Head office

London

Manchester