January 10, 2016 at 7:39 pm
Thank you for explaining the internal storage of datetime vs datetime2. The fact that datetime2 is endian raises an interesting question concerning index performance, i.e. for ordering and seeking by date range. Do you know whether SQL Server's indexing strategy will order and index datetime2 data firstly by the date component (last 3 bytes) and secondly by the time component (leftmost 3 to 5 bytes)?
Eg. If column Transactions.TransactionDateTime is a datetime2 and is indexed, I would expect the following statement to SEEK directly to the relevant records:
SELECT * from Transaction t where t.TransactionDateTime between '2016-01-01' and '2016-01-31'
November 9, 2016 at 4:01 pm
SELECT CONVERT(DATETIME, 42335)
So convenient.
Especially when you need to generate a range of dates from a Tally table.
SELECT CONVERT(DATETIME2, 735930)
Ooops...
SELECT GETDATE() - 1
Easy.
SELECT SYSDATETIME() - 1
Ooops...
Well, if you love jumping through the hoops....
_____________
Code for TallyGenerator
November 9, 2016 at 4:15 pm
Let's create a test table:
SELECT CONVERT(DATETIME, tg.N ) DT, CONVERT(DATETIME2, DATEADD(dd, N, '19000101')) DT2
INTO #Dates
FROM dbo.TallyGenerator(0,1000000, NULL, 1) tg
CREATE INDEX IX_DT ON #Dates(DT)
CREATE INDEX IX_DT2 ON #Dates(DT2)
Now run the performance test:
DECLARE @DT_From datetime, @DT_To datetime
DECLARE @DT2_From datetime2, @DT2_To datetime2
SELECT @DT_From = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
SELECT @DT_To = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) + 30
SELECT @DT2_From = @DT_From
SELECT @DT2_To = @DT_To
SET NOCOUNT ON
SET STATISTICS TIME ON
SELECT d.DT
FROM #Dates d
WHERE DT >= @DT_From AND DT < @DT_To
SELECT d.DT2
FROM #Dates d
WHERE DT2 >= @DT2_From AND DT2 < @DT2_To
SET STATISTICS TIME OFF
Here's what I see on my laptop:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 71 ms.
Remember, if your database is on a cloud, they count and your CPU cycles and charge you for them.
Using datetime2 significantly increases CPU usage.
Make sure the benefits it provides outprice the spendings.
_____________
Code for TallyGenerator
May 26, 2017 at 7:25 am
SSIS (at least through 2012) doesn't recognize DATETIME2 when assigning a value to a DATE variable. It has to be cast to a DATETIME data type.
This has been my experience but then again SSIS data types have always been a bit off from SQL Server data types.
July 10, 2017 at 6:23 pm
Almost all the Answers and Comments I've seen here and elsewhere have been heavy on the DateTime2 Pros and light on the Cons. Here's a recap of all Pros and Cons I've seen plus some crucial Cons I've only seen mentioned once or not at all.[/code] Pros and light on the Cons. Here's a recap of all Pros and Cons I've seen plus some crucial Cons I've only seen mentioned once or not at all.
1.1. More ISO compliant (ISO 8601) (Although, I don’t know how this comes into play in practice besides making you write significantly more code that is significantly less readable / maintainable for a "port" to another RDBMS that is likely never to occur for the life of that code. Other than perhaps Microsoft-provided SQL Server tools and Drivers (if even), are there any apps that actually rely the specific Bit-level representations of the DateTime2 Type (or any other SQL Server Type for that matter)? As long as range and precision > DateTime is needed (which is not likely), the implementation-detail agnostic code that works with DateTime2 will also work with DateTime but not vice versa. Of course, YMMV, but I suspect that *for the life of your code*, this and other Pros below of DateTime2 will not be worth the Cons.).
1.2. More range (1/1/0001 to 12/31/9999 vs. 1/1/1753-12/31/9999) (Although, the extra range, all prior to year 1753, will likely not be used (except perhaps in historical / astronomical apps).).
1.3. Exactly matches the range of .NET’s DateTime Type’s range (although both convert back and forth with no special coding if values are within the target type’s range and precision except for Con # 2.1 below else error / rounding will occur).
1.4. More precision (100 nanosecond vs. 3.33 millisecond aka 0.00333 sec.) (Although, the extra precision will likely not be used.).
1.5. When configured for similar (as in 1 millisec not "same" as in 3.33 millisec) precision as DateTime, uses less space (7 vs. 8 bytes), but then of course, you’d be losing the precision and range benefits.
2.1. When passing a Parameter to a .NET SqlCommand, you must specify System.Data.SqlDbType.DateTime2 if you may be passing a value outside the SQL Server DateTime’s range, because it defaults to System.Data.SqlDbType.DateTime.
2.2. Cannot be implicitly / easily converted to a floating-point numeric (# of days since min date-time) value to do the following to / with it in SQL Server expressions using numeric values and operators:
2.2.1. add or subtract # of days or partial days. Note: Using DateAdd Function as a workaround is not trivial when you're needing to consider multiple if not all parts of the date-time.
2.2.2. take the difference between two date-times for purposes of “age” calculation. Note: You cannot simply use SQL Server’s DateDiff Function instead, because it does not compute age as most people would expect in that if the two date-times happens to cross a calendar / clock date-time boundary of the units specified if even for a tiny fraction of that unit, it’ll return the difference as 1 of that unit vs. 0. For example, the DateDiff in Day’s of two date-times only 1 millisecond apart will return 1 vs. 0 (days) if those date-times are on different calendar days (i.e. “1999-12-31 23:59:59.9999999” and “2000-01-01 00:00:00.0000000”). The same 1 millisecond difference date-times if moved so that they don’t cross a calendar day, will return a “DateDiff” in Day’s of 0 (days).
2.2.3. take the Avg of date-times (in an Aggregate Query) by simply converting to “Float” first and then back again to DateTime.
NOTE: To convert DateTime2 to a numeric, you have to do something like the following formula which still assumes your values are not less than the year 1970 (which means you’re losing all of the extra range plus another 217 years. Note: You may not be able to simply adjust the formula to allow for extra range because you may run into numeric overflow issues.
"25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0" – Source: “ https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html “
Of course, you could also Cast to DateTime first (and if necessary back again to DateTime2), but you'd lose the precision and range (all prior to year 1753) benefits of DateTime2 vs. DateTime which are probably the 2 biggest and also at the same time probably not needed which begs the question why use it when you lose the implicit / easy conversions to floating-point numeric (# of days) for addition / subtraction / "age" (vs. DateDiff) / Avg calcs benefit which is a big one in my experience.
Btw, the Avg of date-times is (or at least should be) an important use case. a) Besides use in getting average duration when date-times (since a common base date-time) are used to represent duration (a common practice), b) it’s also useful to get a dashboard-type statistic on what the average date-time is in the date-time column of a range / group of Rows. c) A standard (or at least should be standard) ad-hoc Query to monitor / troubleshoot values in a Column that may not be valid ever / any longer and / or may need to be deprecated is to list for each value the occurrence count and (if available) the Min, Avg and Max date-time stamps associated with that value.
July 10, 2017 at 7:34 pm
You forgot to mention dreadful performance of DATETIME2.
Any operation from a standard set would take times longer if executed against DATETIME2 comparing to DATETIME.
All the PRO's you listed are pretty useless in real life.
I challenge you to describe a single occasion on your life when actually needed microsecond precision for measuring times. Unless you're working on a hadron collider.
But even then - DATETIME2 would be pretty useless, because even for simplest operation of a speed calculation you need to have duration expressed as a FLOAT number of time units, so it can be used for divisions/multiplications.
As for dates before 1753 - which calendar those dates comply with?
What could be a practical use of dates in a calendar which never existed and was never used by any group of people in the world?
Well, it must be very cool to have a calendar with 17.5 centuries of wrong dates, but it does not have any practical use.
_____________
Code for TallyGenerator
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply