DATETIME2 Data Type: Explained

The DATETIME2 data type is one of several data types used to keep track of date and time information, obviously. Just like any other data type, there is a time and place where DATETIME2 might be the preferred type to use over other date and time types, like it’s close relative DATETIME.

In this tutorial, you will get a complete rundown of the DATETIME2 data type. We will discuss how it is different from regular DATETIME, and also some of the quirky things about DATETIME2 that make it an ideal data type in some situations.

We will discuss the following topics about DATETIME2.

1. The date range of DATETIME2
2. Specifying a Precision
3. Examples of using DATETIME2
4. Storage size
5. Accuracy of DATETIME2
6. Tips and tricks

Let’s get into it.

1. The date range of DATETIME2

The first question you might have about DATETIME2 is “What’s it’s date range?” It is:

January 1, 0001 – December 31, 9999

That’s quite the range. Compare that to the regular DATETIME range of January 1, 1753 – December 31, 9999. If you need to store a date and time value older than January 1 1753, you should consider using DATETIME2.

Of course, there is also the Time aspect to understand. The time value that gets stored is based on 24hr format. So a time of 3:30 PM will show as 15:30.

You also need to understand the format that will be used to store the date and time information. By default, when you store a date and time value in a DATETIME2 data type, the format will be:

YYYY-MM-DD hh:mm:ss[.fractional seconds]

We’ll discuss the [fractional seconds] in the next section.

So the Year value is stored in a 4 digit format, like “1971” vs just “71“. This obviously helps eliminate some ambiguity about the Year value.

All other numbers (Month, Day, Hour, Minute, Second) are stored in a 2 digit format.

2. Specifying a Precision

When you create a variable or column that uses the DATETIME2 data type, you can choose to specify something called the fractional seconds precision.

First, understand there are obviously fractions of a second that can be measured. We might want to keep track of those fractions of a second in cases where we want to store extremely accurate mathematic or scientific calculations, for example.

Fractions of a second are tracked in base ten. When we create a DATETIME2 variable or column, we can specify how many decimal places of a second we want to track. The range you can specify is 0 through 7.

You would specify the precision you want in parentheses next to the DATETIME2 keyword. For example, if you want to create a DATETIME2 variable called “OrderDate” that stores up to 3 decimal places of a second, you would type:

DECLARE @OrderDate DATETIME2(3)

So this variable could hold a date like 2021-05-1 14:30:31.788, or 2020-12-17 09:13:51.334. 

We cannot store more than 3 decimal places of a second in our OrderDate variable. For example, we can’t store a value like 2021-03-06 12:44:20.3459. That’s 4 decimal places of a second, which won’t fit.

You aren’t required to specify a precision value, either. If you leave off a precision value, the precision will default to 7. Take this example:

DECLARE @ShipDate DATETIME

We didn’t specify a precision, so the precision defaults to 7. We can store a value like 2021-06-15 08:12:33.1234567 just fine. When you leave off a precision value, I guess SQL Server assumes you would prefer to be more accurate than less.

And remember, if you outline a precision, you are limited to between 0 and 7. If you try to outline 8, for example, you will get an error message:

3. Examples of using DATETIME2

Let’s actually create a simple table with a DATETIME2 column and populate it with some values.

We’ll create a simple OnlineOrders table:

CREATE TABLE OnlineOrders
(
OrderID INT IDENTITY,
ProductID INT,
CustomerID INT,
OrderDate DATETIME2
)

Remember, since we didn’t outline a precision for our OrderDate column, the precision of a second will default to 7 decimal places.

Now we’ll add a few rows. We’ll populate our OrderDate column three different ways:

1. Using GETDATE().
2. Manually typing an entire date and time value, up to the seventh decimal of a fractional second.
3. Typing a date with no time value at all.

Here it is:

--Using GETDATE()
INSERT INTO OnlineOrders (ProductID, CustomerID, OrderDate)
VALUES (15, 300, GETDATE())

--Manually typing a full date and time value
INSERT INTO OnlineOrders (ProductID, CustomerID, OrderDate)
VALUES (13, 200, '2021-05-01 15:30:05.1234567')

--Only specifying a date
INSERT INTO OnlineOrders (ProductID, CustomerID, OrderDate)
VALUES (11, 250, '4/15/2021')

Here are the results (obviously the date in your first row will be different from my first row):

Notice all the dates show 7 decimals of a second. We understand why, right?

Let’s talk about the first value we got from the GETDATE() system function. GETDATE() will return the current date and time as a DATETIME data type. This data type is slightly less accurate.

DATETIME will only store up to three decimal places of a second. Furthermore, the third decimal place is rounded to increments of .000, .003 or .007 seconds. It looks like in my case, it rounded the third digit to a zero.

The value in our second row is fairly self explanatory. We manually typed the value we wanted to store in our OrderDate column, and specified absolutely everything we could, including all 7 decimals of a second.

The third value is interesting. When that value was inserted, we didn’t specify a time value at all. We can see that in cases where a value isn’t specified, SQL Server simply uses zero for that space.

4. Storage size

You might be asking yourself “Why would I ever specify a precision value? I understand the precision will default to 7, and it’s always better to be more precise than not, so who cares?”

If you leave off a precision value, your date and time values might be using more storage space than they need to.

Here’s the thing: If you have a DATETIME2 value with a precision between 0 and 2, the value will take up 6 bytes of storage.

So I mean the following data types:

DATETIME2(0)
DATETIME2(1)
DATETIME2(2)

Instead, if you have a DATETIME2 value with a precision of 3 or 4, the value will take up 7 bytes of storage.

I mean the following data types:

DATETIME2(3)
DATETIME2(4)

Finally, a precision between 5 and 7 will use 8 bytes of storage.

I mean these types:

DATETIME2(5)
DATETIME2(6)
DATETIME2(7)
DATETIME2 --We understand this will default to DATETIME2(7)

Pop Quiz:

What should we do if want to add a ShipDates column to our table, and we don’t care about fractions of a second?

Which statement should we run? This?:

ALTER TABLE OnlineOrders ADD ShipDate DATETIME2

Or this?:

ALTER TABLE OnlineOrders ADD ShipDate DATETIME2(0)

If you chose option 2, you get a gold star. Since we don’t care about fractions of a second, we should use DATETIME2(0) because no decimal places of a second will be stored, and it will use 6 bytes for every value.

Instead, if we omitted the precision, we would be storing 7 decimals of a second in 8 bytes of space. Not good!

And let’s think about why we wouldn’t want to use the regular DATETIME data type either.

Remember, regular DATETIME is going to be precise up to 3 decimals of a second, whether we want it to or not. And even worse, it always uses 8 bytes of storage to store a value.

Therefore, if we used regular DATETIME for our new ShipDate column, we would be storing more information than we care about, and using more space than we need to.

Do you see why it’s important to know a thing or two about data types?

5. Accuracy of DATETIME2

This data type is very accurate. It will store a date and time value within 100 nanoseconds.

To understand what that means, take a look at this value:

31.123456789

That red digit is the nanosecond, which is the ninth digit after the decimal.

But remember, DATETIME2 will only store up to 7 digits after the decimal. Up to this digit:

31.123456789

So, what does SQL Server do with those last two digits? It cuts them off and rounds the 7th digit. If we were to try storing that value, it would be stored as 31.1234568.

That is why we say DATETIME2 is accurate up to 100 nanoseconds. Our new number is basically 31.123456800 which is only 11 nanoseconds away from the actual value of 31.123456789. Nothing to cry about.

6. Tips and tricks

Here are two helpful tips you should know when working with the DATETIME2 data type:

Tip # 1: DATETIME2 was introduced in SQL Server 2008

If you need to use a date and time data type for older versions, you’ll need to stick with the legacy DATETIME data type.

Tip # 2: The default Precision is 7

Remember, if you don’t specify a precision for DATETIME2 when using it for a column or variable, the precision will default to 7.

Next Steps:

Leave a comment if you found this tutorial helpful!

As you’ve learned, it’s very important to understand the differences between the data types available to us in SQL Server. The following FREE Ebook will get you started:

FREE EBook on SQL Server Data Types!

This guide provides a very simple rundown on several useful data types used in Microsoft SQL Server. Definitely download it today!

Another set of data types you should understand are the character string data types.

Choosing the wrong character string data type can mean the difference between your database having an efficient use of space or a wasteful use of space.

Make sure you understand the differences between all the character data types by reading this tutorial:

SQL Server character data types (and the differences between them)

Also, I have a full tutorial discussing all the integer data types, and the differences between them. If you want to understand all the different integer data types available to us, definitely check out that tutorial here:

The Integer Data Types: Everything You Need To Know

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!