# Combine DATE and TIME to DATETIME2 in SQL Server

• Comments posted to this topic are about the item Combine DATE and TIME to DATETIME2 in SQL Server

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Thanks for the interesting post!

Could you explain the why varbinary is used for the time but binary is used for the date?  I tried using binary for both (albeit with minimal testing) and it seems to work correctly.

• Thanks, Jeff for this very good writeup (as always)!

😎

Just a quick point, DATETIME2 is defaulted to DATETIME2(7), not considered the same data type as DATETIME2(<>7), same goes with the TIME data type, which defaults to TIME(7) if the precision is not specified!

Given that the data type is DATETIME2(7), we can assert the following:

The DATE data type is 3 Bytes big-endian, the TIME(7) is 6 Bytes big-endian and the DATETIME2(7) is 9 Bytes Big-endian, the two former can be combined as TIME(7) + DATE= DATETIME2(7).

The fastest conversion method from the date and time is then a binary concatenation of the two values, as it only entails converting "numerical" values to binary values and vici versa.

Cannot access my original test harness at the moment, will post it when I can, here is the conversion code (as far as I remember)

(Hint, using a CTE in the code exposes the operations in the execution plan)

`USE TEEST;GOSET NOCOUNT ON;GODECLARE @DT2 DATETIME2(7) = '20230101 01:01:00.0000001';DECLARE @DT  DATE         = '20230101';DECLARE @T   TIME(7)      = '01:01:00.0000001';;WITH TIME_TABLE AS(SELECT      @DT2                          AS DATETIME2_7    ,CONVERT(BINARY(9),@DT2,0)     AS BIN_DATETIME2_7    ,@DT                           AS DATE_X    ,CONVERT(BINARY(3),@DT,0)      AS BIN_DATE_X    ,@T                            AS TIME7_X    ,CONVERT(BINARY(6),@T,0)       AS BIN_TIME7_X    ,CONVERT(VARBINARY(9),CONCAT(CONVERT(BINARY(6),@T,0),CONVERT(BINARY(3),@DT,0)),0) AS BINCONCAT_DT2_7    ,CONVERT(DATETIME2(7),CONVERT(BINARY(9),CONCAT(CONVERT(BINARY(6),@T,0),CONVERT(BINARY(3),@DT,0)),0),0) AS CONCAT_DT2_7    ,CONVERT(DATETIME2(7),CONVERT(BINARY(9),CONVERT(BINARY(6),@T,0)+CONVERT(BINARY(3),@DT,0)),0) AS CONCAT_DT2_7C)SELECT    TT.DATETIME2_7   ,TT.BIN_DATETIME2_7   ,TT.DATE_X   ,TT.BIN_DATE_X   ,TT.TIME7_X   ,TT.BIN_TIME7_X   ,BINCONCAT_DT2_7   ,CONCAT_DT2_7   ,CONCAT_DT2_7CFROM TIME_TABLE TT;`

Output

`DATETIME2_7                 BIN_DATETIME2_7      DATE_X     BIN_DATE_X TIME7_X          BIN_TIME7_X    BINCONCAT_DT2_7      CONCAT_DT2_7                CONCAT_DT2_7C--------------------------- -------------------- ---------- ---------- ---------------- -------------- -------------------- --------------------------- ---------------------------2023-01-01 01:01:00.0000001 0x0701AE878508D8440B 2023-01-01 0xD8440B   01:01:00.0000001 0x0701AE878508 0x0701AE878508D8440B 2023-01-01 01:01:00.0000001 2023-01-01 01:01:00.0000001`

Edited: corrected the completion of the code posted.

• w1tchf1nd3r wrote:

Thanks for the interesting post!

Could you explain the why varbinary is used for the time but binary is used for the date?  I tried using binary for both (albeit with minimal testing) and it seems to work correctly.

Thanks for the feedback and the great question.

The reason why is that I used VARBINARY for the time is due to "Human Factors".  Not everyone will make a "pure" TIME column, which defaults to TIME(7).  Some may have a TIME(3) column in an improper attempt to be able to support the DATETIME datatype.  Others may be tempted to use TIME(0) to save space.

The VARBINARY() auto-magically takes that into account for the TIME element.  When the TIME is stored as any form of binary, it stores a "lead byte" that contains the number of digits that the TIME element represents.  I've not tested if using a fixed width binary for time would actually work correctly for sub-second resolutions of less than 7 digits. (See edit below... )

As for DATE, date is always exactly 3 bytes.  The conversion there is a very appropriate fixed-width BINARY(3).  You could probably change it to a VARBINARY(3) with no harm but I don't see a reason to.

It'll be interesting to test all of that.  I just didn't test for that for this article where I only went the "right-sized" method.

EDIT:  I just tested it.  A BINARY(6) only works for TIME(7) .  If you pass it a TIME(3) and do the same concatenation as in the original formula, it returns the following error.

Msg 241, Level 16, State 1, Line 9

Conversion failed when converting date and/or time from character string.

On the 10 million row test code included in the attached ZIP file, the differences between the two methods are virtually identical and each takes turns winning.

Given that the BINARY(6) method will cause a failure due to "Human Factors", I'll stick with the VARBINARY(6) method used in the article to avoid such unnecessary errors. 😀

• This reply was modified 1 month, 3 weeks ago by  Jeff Moden. Reason: Add conclusion for addition testing done
• This reply was modified 1 month, 3 weeks ago by  Jeff Moden. Reason: Added note about why DATE wasn't converted using a VARBINARY()

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Eirikur Eiriksson wrote:

Thanks, Jeff for this very good writeup (as always)! 😎

Just a quick point, DATETIME2 is defaulted to DATETIME2(7), not considered the same data type as DATETIME2(<>7), same goes with the TIME data type, which defaults to TIME(7) if the precision is not specified!

Given that the data type is DATETIME2(7), we can assert the following: The DATE data type is 3 Bytes big-endian, the TIME(7) is 6 Bytes big-endian and the DATETIME2(7) is 9 Bytes Big-endian, the two former can be combined as TIME(7) + DATE= DATETIME2(7). The fastest conversion method from the date and time is then a binary concatenation of the two values, as it only entails converting "numerical" values to binary values and vici versa.

Cannot access my original test harness at the moment, will post it when I can, here is the conversion code (as far as I remember)

(Hint, using a CTE in the code exposes the operations in the execution plan)

`USE TEEST;GOSET NOCOUNT ON;GODECLARE @DT2 DATETIME2(7) = '20230101 01:01:00.0000001';DECLARE @DT  DATE         = '20230101';DECLARE @T   TIME(7)      = '01:01:00.0000001';;WITH TIME_TABLE AS(SELECT      @DT2                          AS DATETIME2_7    ,CONVERT(BINARY(9),@DT2,0)     AS BIN_DATETIME2_7    ,@DT                           AS DATE_X    ,CONVERT(BINARY(3),@DT,0)      AS BIN_DATE_X    ,@T                            AS TIME7_X    ,CONVERT(BINARY(6),@T,0)       AS BIN_TIME7_X    ,CONVERT(VARBINARY(9),CONCAT(CONVERT(BINARY(6),@T,0),CONVERT(BINARY(3),@DT,0)),0) AS BINCONCAT_DT2_7    ,CONVERT(DATETIME2(7),CONVERT(BINARY(9),CONCAT(CONVERT(BINARY(6),@T,0),CONVERT(BINARY(3),@DT,0)),0),0) AS CONCAT_DT2_7    ,CONVERT(DATETIME2(7),CONVERT(BINARY(9),CONVERT(BINARY(6),@T,0)+CONVERT(BINARY(3),@DT,0)),0) AS CONCAT_DT2_7C)SELECT    TT.DATETIME2_7   ,TT.BIN_DATETIME2_7   ,TT.DATE_X   ,TT.BIN_DATE_X   ,TT.TIME7_X   ,TT.BIN_TIME7_X   ,BINCONCAT_DT2_7   ,CONCAT_DT2_7   ,CONCAT_DT2_7CFROM TIME_TABLE TT;`

Output

`DATETIME2_7                 BIN_DATETIME2_7      DATE_X     BIN_DATE_X TIME7_X          BIN_TIME7_X    BINCONCAT_DT2_7      CONCAT_DT2_7                CONCAT_DT2_7C--------------------------- -------------------- ---------- ---------- ---------------- -------------- -------------------- --------------------------- ---------------------------2023-01-01 01:01:00.0000001 0x0701AE878508D8440B 2023-01-01 0xD8440B   01:01:00.0000001 0x0701AE878508 0x0701AE878508D8440B 2023-01-01 01:01:00.0000001 2023-01-01 01:01:00.0000001`

Edited: corrected the completion of the code posted.

Hi, Eirikur and thanks for the feedback/discussion.

See the above where I explain why I used the VARBINARY() method to accommodate the "Human Factor" eventuality.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden wrote:

Eirikur Eiriksson wrote:

Thanks, Jeff for this very good writeup (as always)! 😎

Just a quick point, DATETIME2 is defaulted to DATETIME2(7), not considered the same data type as DATETIME2(<>7), same goes with the TIME data type, which defaults to TIME(7) if the precision is not specified!

Given that the data type is DATETIME2(7), we can assert the following: The DATE data type is 3 Bytes big-endian, the TIME(7) is 6 Bytes big-endian and the DATETIME2(7) is 9 Bytes Big-endian, the two former can be combined as TIME(7) + DATE= DATETIME2(7). The fastest conversion method from the date and time is then a binary concatenation of the two values, as it only entails converting "numerical" values to binary values and vici versa.

Cannot access my original test harness at the moment, will post it when I can, here is the conversion code (as far as I remember)

(Hint, using a CTE in the code exposes the operations in the execution plan)

`USE TEEST;GOSET NOCOUNT ON;GODECLARE @DT2 DATETIME2(7) = '20230101 01:01:00.0000001';DECLARE @DT  DATE         = '20230101';DECLARE @T   TIME(7)      = '01:01:00.0000001';;WITH TIME_TABLE AS(SELECT      @DT2                          AS DATETIME2_7    ,CONVERT(BINARY(9),@DT2,0)     AS BIN_DATETIME2_7    ,@DT                           AS DATE_X    ,CONVERT(BINARY(3),@DT,0)      AS BIN_DATE_X    ,@T                            AS TIME7_X    ,CONVERT(BINARY(6),@T,0)       AS BIN_TIME7_X    ,CONVERT(VARBINARY(9),CONCAT(CONVERT(BINARY(6),@T,0),CONVERT(BINARY(3),@DT,0)),0) AS BINCONCAT_DT2_7    ,CONVERT(DATETIME2(7),CONVERT(BINARY(9),CONCAT(CONVERT(BINARY(6),@T,0),CONVERT(BINARY(3),@DT,0)),0),0) AS CONCAT_DT2_7    ,CONVERT(DATETIME2(7),CONVERT(BINARY(9),CONVERT(BINARY(6),@T,0)+CONVERT(BINARY(3),@DT,0)),0) AS CONCAT_DT2_7C)SELECT    TT.DATETIME2_7   ,TT.BIN_DATETIME2_7   ,TT.DATE_X   ,TT.BIN_DATE_X   ,TT.TIME7_X   ,TT.BIN_TIME7_X   ,BINCONCAT_DT2_7   ,CONCAT_DT2_7   ,CONCAT_DT2_7CFROM TIME_TABLE TT;`

Output

`DATETIME2_7                 BIN_DATETIME2_7      DATE_X     BIN_DATE_X TIME7_X          BIN_TIME7_X    BINCONCAT_DT2_7      CONCAT_DT2_7                CONCAT_DT2_7C--------------------------- -------------------- ---------- ---------- ---------------- -------------- -------------------- --------------------------- ---------------------------2023-01-01 01:01:00.0000001 0x0701AE878508D8440B 2023-01-01 0xD8440B   01:01:00.0000001 0x0701AE878508 0x0701AE878508D8440B 2023-01-01 01:01:00.0000001 2023-01-01 01:01:00.0000001`

Edited: corrected the completion of the code posted.

Hi, Eirikur and thanks for the feedback/discussion.

See the above where I explain why I used the VARBINARY() method to accommodate the "Human Factor" eventuality.

Jeff Moden wrote:

Eirikur Eiriksson wrote:

Thanks, Jeff for this very good writeup (as always)! 😎

Just a quick point, DATETIME2 is defaulted to DATETIME2(7), not considered the same data type as DATETIME2(<>7), same goes with the TIME data type, which defaults to TIME(7) if the precision is not specified!

Given that the data type is DATETIME2(7), we can assert the following: The DATE data type is 3 Bytes big-endian, the TIME(7) is 6 Bytes big-endian and the DATETIME2(7) is 9 Bytes Big-endian, the two former can be combined as TIME(7) + DATE= DATETIME2(7). The fastest conversion method from the date and time is then a binary concatenation of the two values, as it only entails converting "numerical" values to binary values and vici versa.

Cannot access my original test harness at the moment, will post it when I can, here is the conversion code (as far as I remember)

(Hint, using a CTE in the code exposes the operations in the execution plan)

`USE TEEST;GOSET NOCOUNT ON;GODECLARE @DT2 DATETIME2(7) = '20230101 01:01:00.0000001';DECLARE @DT  DATE         = '20230101';DECLARE @T   TIME(7)      = '01:01:00.0000001';;WITH TIME_TABLE AS(SELECT      @DT2                          AS DATETIME2_7    ,CONVERT(BINARY(9),@DT2,0)     AS BIN_DATETIME2_7    ,@DT                           AS DATE_X    ,CONVERT(BINARY(3),@DT,0)      AS BIN_DATE_X    ,@T                            AS TIME7_X    ,CONVERT(BINARY(6),@T,0)       AS BIN_TIME7_X    ,CONVERT(VARBINARY(9),CONCAT(CONVERT(BINARY(6),@T,0),CONVERT(BINARY(3),@DT,0)),0) AS BINCONCAT_DT2_7    ,CONVERT(DATETIME2(7),CONVERT(BINARY(9),CONCAT(CONVERT(BINARY(6),@T,0),CONVERT(BINARY(3),@DT,0)),0),0) AS CONCAT_DT2_7    ,CONVERT(DATETIME2(7),CONVERT(BINARY(9),CONVERT(BINARY(6),@T,0)+CONVERT(BINARY(3),@DT,0)),0) AS CONCAT_DT2_7C)SELECT    TT.DATETIME2_7   ,TT.BIN_DATETIME2_7   ,TT.DATE_X   ,TT.BIN_DATE_X   ,TT.TIME7_X   ,TT.BIN_TIME7_X   ,BINCONCAT_DT2_7   ,CONCAT_DT2_7   ,CONCAT_DT2_7CFROM TIME_TABLE TT;`

Output

`DATETIME2_7                 BIN_DATETIME2_7      DATE_X     BIN_DATE_X TIME7_X          BIN_TIME7_X    BINCONCAT_DT2_7      CONCAT_DT2_7                CONCAT_DT2_7C--------------------------- -------------------- ---------- ---------- ---------------- -------------- -------------------- --------------------------- ---------------------------2023-01-01 01:01:00.0000001 0x0701AE878508D8440B 2023-01-01 0xD8440B   01:01:00.0000001 0x0701AE878508 0x0701AE878508D8440B 2023-01-01 01:01:00.0000001 2023-01-01 01:01:00.0000001`

Edited: corrected the completion of the code posted.

Hi, Eirikur and thanks for the feedback/discussion.

See the above where I explain why I used the VARBINARY() method to accommodate the "Human Factor" eventuality.

He he he, I must be getting old, totally missed that thing you call a "Human Factor", might need some explanation on the concept. Is that some kind of a carbon life form property?

😎

• @Eirikur,

The "Human Factor" is why I value QA Testers so much (and, MAN!  They taught me how to anticipate user actions, also known politely as "Human Factors").  In this case, if I'd not already anticipated it, they would have told me "That's great, Jeff, but what if people don't use the defaults for the TIME column and use something like TIME(3) or TIME(0), instead"?

The answer, of course, is it will break.

The speed was such an improvement that I didn't even both testing it with BINARY() until you brought it up. I was tickled that there was no discernible difference in performance (kinda figured that but had to be sure).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• This was removed by the editor as SPAM

• Eirikur Eiriksson wrote:

Given that the data type is DATETIME2(7), we can assert the following: The DATE data type is 3 Bytes big-endian, the TIME(7) is 6 Bytes big-endian and the DATETIME2(7) is 9 Bytes Big-endian, the two former can be combined as TIME(7) + DATE= DATETIME2(7). The fastest conversion method from the date and time is then a binary concatenation of the two values, as it only entails converting "numerical" values to binary values and vici versa.

There's an error in your information about datetime2(7). You state that datetime2(7) is 9 Bytes. This is incorrect. datetime2(5), datetime2(6), and datetime2(7) all use 8 bytes. When converting to varbinary varbinary(9) is required because one byte is needed to store the precision.

• Jonathan AC Roberts wrote:

Eirikur Eiriksson wrote:

Given that the data type is DATETIME2(7), we can assert the following: The DATE data type is 3 Bytes big-endian, the TIME(7) is 6 Bytes big-endian and the DATETIME2(7) is 9 Bytes Big-endian, the two former can be combined as TIME(7) + DATE= DATETIME2(7). The fastest conversion method from the date and time is then a binary concatenation of the two values, as it only entails converting "numerical" values to binary values and vici versa.

There's an error in your information about datetime2(7). You state that datetime2(7) is 9 Bytes. This is incorrect. datetime2(5), datetime2(6), and datetime2(7) all use 8 bytes. When converting to varbinary varbinary(9) is required because one byte is needed to store the precision.

I could be wrong but I'm pretty sure he was talking about the storage values of when they were converted to binaries, in which case, he's correct.

It IS a bit confusing because he makes it sound like the DATATYPE lengths rather than the converted binary lengths.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff Moden wrote:

Jonathan AC Roberts wrote:

Eirikur Eiriksson wrote:

Given that the data type is DATETIME2(7), we can assert the following: The DATE data type is 3 Bytes big-endian, the TIME(7) is 6 Bytes big-endian and the DATETIME2(7) is 9 Bytes Big-endian, the two former can be combined as TIME(7) + DATE= DATETIME2(7). The fastest conversion method from the date and time is then a binary concatenation of the two values, as it only entails converting "numerical" values to binary values and vici versa.

There's an error in your information about datetime2(7). You state that datetime2(7) is 9 Bytes. This is incorrect. datetime2(5), datetime2(6), and datetime2(7) all use 8 bytes. When converting to varbinary varbinary(9) is required because one byte is needed to store the precision.

I could be wrong but I'm pretty sure he was talking about the storage values of when they were converted to binaries, in which case, he's correct.

It IS a bit confusing because he makes it sound like the DATATYPE lengths rather than the converted binary lengths.

Yes, maybe, but it doesn't read like that. The original statement appears to be referring to the byte size of the DATETIME2(7) data type, not the binary conversion. It's worth clarifying the context to avoid misunderstandings.

• Jonathan AC Roberts wrote:

Jeff Moden wrote:

Jonathan AC Roberts wrote:

Eirikur Eiriksson wrote:

Given that the data type is DATETIME2(7), we can assert the following: The DATE data type is 3 Bytes big-endian, the TIME(7) is 6 Bytes big-endian and the DATETIME2(7) is 9 Bytes Big-endian, the two former can be combined as TIME(7) + DATE= DATETIME2(7). The fastest conversion method from the date and time is then a binary concatenation of the two values, as it only entails converting "numerical" values to binary values and vici versa.

There's an error in your information about datetime2(7). You state that datetime2(7) is 9 Bytes. This is incorrect. datetime2(5), datetime2(6), and datetime2(7) all use 8 bytes. When converting to varbinary varbinary(9) is required because one byte is needed to store the precision.

I could be wrong but I'm pretty sure he was talking about the storage values of when they were converted to binaries, in which case, he's correct.

It IS a bit confusing because he makes it sound like the DATATYPE lengths rather than the converted binary lengths.

Yes, maybe, but it doesn't read like that. The original statement appears to be referring to the byte size of the DATETIME2(7) data type, not the binary conversion. It's worth clarifying the context to avoid misunderstandings.

Like I said above, "It IS a bit confusing because he makes it sound like the DATATYPE lengths rather than the converted binary lengths."  I totally agree that someone that just drops in on his post would come to the wrong conclusion and maybe even confuse a couple of us that have been following along all the time.  Like I said "I could be wrong but..." 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• He he he, guilty as charged, made the assumption that it was clear that I was referring to the binary variable sizes given the code I posted.

😎

This is one of the things I love about this community, everything posted is immediately under a peer review, thanks Jonathan for pointing out the unclarity in my post!

• I find it interesting that this doesn't work when using the correct zero date for a datetime2:

`DATEADD(dd,DATEDIFF(dd,'1900',DateValue),CONVERT(DATETIME2,TimeValue))          --2nd Fastest`

The correct zero date for a DATETIME2 is '0001-01-01'.

It looks to me like the CAST/CONVERT of a time to a datetime2 is broken, forcing you to specify the year 1900 in the DATEDIFF to get the correct offset to add to the DATETIME2 because CAST/CONVERT of the time results in 1900-01-01 {time}

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

• Jeffrey Williams wrote:

I find it interesting that this doesn't work when using the correct zero date for a datetime2:

`DATEADD(dd,DATEDIFF(dd,'1900',DateValue),CONVERT(DATETIME2,TimeValue))          --2nd Fastest`

The correct zero date for a DATETIME2 is '0001-01-01'.

It looks to me like the CAST/CONVERT of a time to a datetime2 is broken, forcing you to specify the year 1900 in the DATEDIFF to get the correct offset to add to the DATETIME2 because CAST/CONVERT of the time results in 1900-01-01 {time}

I totally agree there!  The "new" datetime stuff uses the DATETIME epoch instead of the DATETIME2 epoch.  That and the fact that even in SQL Server 2022, they still have no DATEADD_BIG, which would greatly simplify a whole lot of things.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.