SQL Server, datetime vs. datetime2

Hi guys,

This time, in this post we will speak about datetime and datetime2 datatypes. We will talk about the pros and cons. We will talk about all the differences between the two types of data. Write me in the comments which type of data do you prefer between the two.

Enjoy the reading!

 

 

Datetime Vs. Datetime2

First of all, it must be said that the datatype datetime is certainly the best known native format for storing a date in SQL Server.

However, Microsoft has also been introducing a new type of data for many years now, also used to manage dates.
It is in fact from SQL server 2008 that it is present in datetime2 data type.

This "new" type of data was born primarily to have higher accuracy.

Let's in fact talk about ...precision!

Datetime

Certainly the datetime data type is not very precise and certainly there are many cases in which its precision is simply not sufficient.

Let's see it with an example.

Open the SQL Management studio (SSMS) and write the following commands:

Select cast('2022-04-16 02:55:19.307' as datetime), cast(cast('2022-04-16 02:55:19.307' as datetime) as varbinary) 
Select cast('2022-04-16 02:55:19.308' as datetime), cast(cast('2022-04-16 02:55:19.308' as datetime) as varbinary) 
Select cast('2022-04-16 02:55:19.309' as datetime), cast(cast('2022-04-16 02:55:19.309' as datetime) as varbinary) 
Select cast('2022-04-16 02:55:19.310' as datetime), cast(cast('2022-04-16 02:55:19.310' as datetime) as varbinary) 
   

Now run them.

(Be careful that the dates are in English format and therefore the language must also be set in English. You can do it typing the command: set language english)

By looking below it is clear that the for datetime datetype the minimum date difference is about 3 tenths of a thousandth of a second.

Note that you can not store the date 2022-04-16 02:55:19:308 or 2022-04-16 02:55:19:309.

A date stored in datetime format uses 8 bytes.

Select cast( 0x0000AE7900302750 as datetime)  -- 2022-04-16 02:55:19:307
Select cast( 0x0000AE7900302751 as datetime)  -- 2022-04-16 02:55:19:310 

Where the first 4 bytes represent the date (without the time) and the last 4 bytes represent the time:

Select cast( 0x0000AE7900302750 as datetime)  -- 2022-04-16 00:00:00:000
Select cast( 0x0000AE7900302750 as datetime)  -- 1900-01-01 02:55:19:307

Datetime2

If the precision of the datetime type is not sufficient for you, we can use the datime2 data type that was created for this purpose.

Let's write now:

Select cast('2022-04-16 02:55:19.3070000' as datetime2), cast(cast('2022-04-16 02:55:19.3070000' as datetime2) as varbinary)
select cast('2022-04-16 02:55:19.3070001' as datetime2), cast(cast('2022-04-16 02:55:19.3070001' as datetime2) as varbinary)

We can note that this time we can specify 7 decimals. 

Yes, we can store 0.0000001 seconds as the smallest unit of time

Moreover, this new type of data also has these advantages:

• is ANSI SQL and ISO Compliant (ISO 8601)
• depending on the precision can occupy from 6 to 8 bytes.

A datetime2 is stored is this format:
 

Until now we have only seen advantages.
However, we must also talk about the disadvantages!

Let me say that the datetime2 when it was born in 2008 brought with it some problems.

Problems related to the estimation of cardinality.

Let's take an example but remember that to try it at home you need a SQL Server 2008. 

In later versions this problem has been solved.

 

Datetime2 cardinality estimate issues

 

For this example, let's create two tables that have the same structure:

• One Id field of integer type.
• A second field of type datetime in the first table and of type datetyime2 in the second table.
• An index of the Dt field in each table.

So, open the SQL Server Management studio and type:

Create table Test_with_datetime (id int identity(1,1), dt datetime)
Create index idx_Test_wih_datetime_dt on Test_wih_datetime(dt)

Create table Test_with_datetime2 (id int identity(1,1), dt datetime2)
Create index idx_Test_wih_datetime2_dt on Test_wih_datetime2(dt)

Now populate the two tables with some data:

Insert into Test_with_datetime2(dt)
  Select top 1000000 SYSDATETIME() from sys.objects o
  join sys.objects o2 on o.object_id <> o2.object_id
  join sys.objects o3 on o.object_id <> o3.object_id

Insert into Test_with_datetime(dt)
  Select dt from Test_wih_datetime2

Now we can try this SELECT command on the table with the datetime field.

We want to extract all the rows of the table where the date in the dt field is greater or equal to the actual data plus 11 days.

SELECT dt FROM Test_with_datetime
WHERE dt >= DATEADD(DAY, 11, GETUTCDATE());

let's look at the execution plan and in detail the estimated number of rows:

we can see that the optimizer did a good job: the estimated number of rows is almost identical to the real number of extracted rows (55.408 vs. 53.598)

Now let's try to execute the same select with the DATEADD function in the where clause but on the table with the Dt field of type datetime2.

This time using the datetime2 field the extimated number of rows is not correct.

The extimated number of rows is always equal to the number of rows of the tables.

As usually incorrect extimate on the number of rows is a problem.

It is a problem because could led to the generation of a suboptimal execution plan.

for example i could have few rows and so i could use a nested loop operator for a join instead i use a merge or hash join because the optimizer extimate a large number of rows.

Using a Merge or a Hash with few rows required more execution time than a nested loop.

How to use the Datetime2

 

We usually worked with the datetime type by adding or subtracting a value.
With the datetime2 type you can no longer add and subtract and for me this is the most significant disadvantage.

If i run this command:

Update Test_with_datetime2 set dt = dt + 1.0 / 10000000

I get the following error message:

Msg 206, Level 16, State 2, Line 31
Operand type clash: datetime2 is incompatible with numeric

you can not add a value!

Even trying to do this:

Update Test_wih_datetime2 set dt = dt + cast( id / 10000000 as datetime2)

I get another error message:

Msg 529, Level 16, State 2, Line 41
Explicit conversion from data type int to datetime2 is not allowed.

So, what is the right way?

You should use the DATEADD function.

Update Test_wih_datetime2 set dt = DATEADD(SECOND,  id,  dt)

That'all for today!

Stay tuned for the next post and support me 

Previous post: SPEED NEWS! SQL Server 2022 is coming! when will it be available?