This page has 0 threads | Add post

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

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

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

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

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.

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	New to SQL Server 2008, this data type gives a larger range and more accurate measurement of time.
DATE	0001-01-01 to 9999-12-31	Also new to SQL Server 2008, this allows you to store a date without a time component.
TIME	00:00:00.0000000 to 23:59:59.9999999	New in SQL Server 2008, 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	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.

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

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

Google Sheets

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

Date and Time Data Types in SQL Server

Date and Time Data Types

Entering Dates and Times into a Table

Head office

London

Manchester