March 16, 2016 at 11:29 am
I inherited some old code with what appears to be some kind of shorthand/shortcut for doing DATEADD(day, x, x). It looks like this:
SELECT [datetime]-[smallint];
Am I correct in assuming that is either a deprecated or unsupported syntax? The reason it came to my attention today was that I have been changing some of our DATETIME fields to DATETIME2, and I noticed that short syntax no longer works with DATETIME2. Here's a proof:
DECLARE @DateTime DATETIME = GETDATE();
DECLARE @DateTime2 DATETIME2(0) = GETDATE();
DECLARE @NumDays SMALLINT = 1;
-- Works:
SELECT @DateTime-@NumDays;
-- Fails:
SELECT @DateTime2-@NumDays;
March 16, 2016 at 11:39 am
The datetime and smalldatetime types allowed arithmetic calculations based on the way the values were stored.
The new data types don't allow these operations to allow better functionality.
The functionality is supported, but not for the new types. It's not deprecated.
March 16, 2016 at 11:43 am
DATETIME is stored internally in SQL Server as the number of days since 1/1/1900 (the integer part) plus (I think) a decimal part representing the number of 300ths of a second since midnight. This naturally facilitates integer arithmetic on datetimes.
DATETIME2 is not stored like this.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 16, 2016 at 11:56 am
Got it, thanks for the explanation, guys!
March 16, 2016 at 5:21 pm
When deciding on changing the data types take into consideration that same operations (say, WHERE DateCol1 = DateCol2) take 2-3 time longer on DATETIME2 than on DATETIME.
Do it only where you really need to use the better precision of the new data type.
_____________
Code for TallyGenerator
March 16, 2016 at 6:18 pm
I don't need added precision, in fact I will be using a precision of 0, we don't need milliseconds. But it will be helpful to have the larger range of dates and the decreased storage space. Are date comparisons with DATETIME2 really 2-3 times longer??
March 16, 2016 at 8:05 pm
Luis Cazares (3/16/2016)
The new data types don't allow these operations to allow better functionality.
That's a serious matter of opinion. I think they flubbed it in two ways...
1. They had the true opportunity to make time a true decimal part of the day (which they may have done behind the scenes), which would have allowed for a whole lot more functionality that they currently have including simplistic calculation of durations and the summing of durations.
2. Most programming languages and a serious number of heavy hitter applications (such as Excel) allow for direct date math for good reason... IT MAKES THINGS SIMPLE! Why SQL Server and SQL in general is treated different must be due to some form of zealous relational dogma.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2016 at 7:04 am
Jeff Moden (3/16/2016)
Luis Cazares (3/16/2016)
The new data types don't allow these operations to allow better functionality.That's a serious matter of opinion. I think they flubbed it in two ways...
1. They had the true opportunity to make time a true decimal part of the day (which they may have done behind the scenes), which would have allowed for a whole lot more functionality that they currently have including simplistic calculation of durations and the summing of durations.
2. Most programming languages and a serious number of heavy hitter applications (such as Excel) allow for direct date math for good reason... IT MAKES THINGS SIMPLE! Why SQL Server and SQL in general is treated different must be due to some form of zealous relational dogma.
It's not opinion, Jeff. I wasn't implying that the new data types were better, they just introduced better functionality but they didn't do it the best way. The better functionality is the improvement on precision and range, and the capability to use either time or date. The problems, you've already mentioned them.
March 17, 2016 at 7:52 am
Jeff Moden (3/16/2016)
Luis Cazares (3/16/2016)
The new data types don't allow these operations to allow better functionality.That's a serious matter of opinion. I think they flubbed it in two ways...
1. They had the true opportunity to make time a true decimal part of the day (which they may have done behind the scenes), which would have allowed for a whole lot more functionality that they currently have including simplistic calculation of durations and the summing of durations.
2. Most programming languages and a serious number of heavy hitter applications (such as Excel) allow for direct date math for good reason... IT MAKES THINGS SIMPLE! Why SQL Server and SQL in general is treated different must be due to some form of zealous relational dogma.
This appears to show how it's done behind the scenes:
SELECT
[Now],
NowAsNumber,
Unit = CAST(NowAsNumber AS INT),
Mantissa = NowAsNumber%1,
NowFromNumber = DATEADD(millisecond,24*60*60*1000*(NowAsNumber%1),DATEADD(DAY,(CAST(NowAsNumber AS INT)),0))
FROM ( -- e
SELECT
[Now],
NowAsNumber = CAST([Now] AS NUMERIC(17,12)) -- 17,12 but 13,8 is sufficient to accurately match the original date
FROM (SELECT [Now] = GETDATE()) d
) e
Decimal addition and subtraction work:
SELECT [Now], [Now] + 0.0416666666666667 -- (1/24 of 1 day; 1 hour)
FROM (SELECT [Now] = GETDATE()) d
If you try division you get a datatype mismatch error. Just a hunch but maybe mathematical operations on DATETIME are functionally restricted and it's simpler to throw "Implicit conversion from data type datetime to numeric is not allowed" than "What the heck do you think you're trying to do".
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 17, 2016 at 2:00 pm
Phil Parkin (3/16/2016)
DATETIME is stored internally in SQL Server as the number of days since 1/1/1900 (the integer part) plus (I think) a decimal part representing the number of 300ths of a second since midnight. This naturally facilitates integer arithmetic on datetimes.DATETIME2 is not stored like this.
I'm pretty sure SQL stores datetime as two integers. The first is the number of days since 1900-01-01. The second is the clock ticks past midnight. Since a clock tick was (is?) 3.33 milliseconds, datetime wasn't more accurate than 3ms.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 17, 2016 at 2:10 pm
ScottPletcher (3/17/2016)
Phil Parkin (3/16/2016)
DATETIME is stored internally in SQL Server as the number of days since 1/1/1900 (the integer part) plus (I think) a decimal part representing the number of 300ths of a second since midnight. This naturally facilitates integer arithmetic on datetimes.DATETIME2 is not stored like this.
I'm pretty sure SQL stores datetime as two integers. The first is the number of days since 1900-01-01. The second is the clock ticks past midnight. Since a clock tick was (is?) 3.33 milliseconds, datetime wasn't more accurate than 3ms.
Hmm ... what sort of clock is it that ticks every 3.33 milliseconds? 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 17, 2016 at 2:24 pm
Phil Parkin (3/17/2016)
ScottPletcher (3/17/2016)
Phil Parkin (3/16/2016)
DATETIME is stored internally in SQL Server as the number of days since 1/1/1900 (the integer part) plus (I think) a decimal part representing the number of 300ths of a second since midnight. This naturally facilitates integer arithmetic on datetimes.DATETIME2 is not stored like this.
I'm pretty sure SQL stores datetime as two integers. The first is the number of days since 1900-01-01. The second is the clock ticks past midnight. Since a clock tick was (is?) 3.33 milliseconds, datetime wasn't more accurate than 3ms.
Hmm ... what sort of clock is it that ticks every 3.33 milliseconds? 🙂
One where there are 300 ticks per second, yes?
declare @dt datetime
declare @bin varbinary(max)
set @dt = '2016/03/01 01:02:03'
set @bin = CONVERT(varbinary(max), @dt)
select @dt, @bin
set @dt = '2016/03/01 01:02:04' --add 1 second
set @bin = CONVERT(varbinary(max), @dt)
select @dt, @bin
--add 300 to the binary value, gives same value as adding 1 second
select convert(varbinary, CAST(0xae4 as int) + 300)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 17, 2016 at 2:50 pm
One where there are 300 ticks per second, yes?
Actually, no.
1/0.0033 = 303.03030303......
hardly a round number.
300 ticks per second = 3.3 recurring milliseconds
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply