August 7, 2023 at 12:00 am
Comments posted to this topic are about the item Combine DATE and TIME to DATETIME2 in SQL Server
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2023 at 1:09 pm
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.
August 7, 2023 at 2:23 pm
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;
GO
SET NOCOUNT ON;
GO
DECLARE @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_7C
FROM 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.
August 7, 2023 at 3:04 pm
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. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 7, 2023 at 3:08 pm
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;
GO
SET NOCOUNT ON;
GO
DECLARE @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_7C
FROM 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.0000001Edited: 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
Change is inevitable... Change for the better is not.
August 7, 2023 at 4:35 pm
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;
GO
SET NOCOUNT ON;
GO
DECLARE @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_7C
FROM 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.0000001Edited: 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.
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;
GO
SET NOCOUNT ON;
GO
DECLARE @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_7C
FROM 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.0000001Edited: 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?
😎
August 7, 2023 at 6:04 pm
@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
Change is inevitable... Change for the better is not.
August 7, 2023 at 6:43 pm
This was removed by the editor as SPAM
August 7, 2023 at 10:11 pm
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.
August 7, 2023 at 11:04 pm
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
Change is inevitable... Change for the better is not.
August 7, 2023 at 11:10 pm
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.
August 8, 2023 at 12:54 am
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
Change is inevitable... Change for the better is not.
August 8, 2023 at 11:10 am
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!
August 8, 2023 at 9:17 pm
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
How to post questions to get better answers faster
Managing Transaction Logs
August 9, 2023 at 2:31 pm
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 FastestThe 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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply