DATEADD shortcut

  • 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;


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Got it, thanks for the explanation, guys!


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • 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.

  • 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??


    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • 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


    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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".

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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? πŸ™‚

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply