Wow!!! I didn't know that could be done - Datetime Calculations

  • aochss

    SSCommitted

    Points: 1677

    All,

    I have been programming in T-SQL for a while. I still love what I do since there is still so much to learn. I ran across one of these "Duh, I didn't know that" moments today. You can subtract and add DECIMAL values to a datetime value.

    I do a lot of programming/testing involving batch jobs, data inserts and updates and I need to see the records created/updated in the last day/hour/minutes. I always modified the datetime value in my comparison to get the results until I tried subtracting a decimal value from getdate().

    WOW!!! Do I love this stuff. Hope this helps.

    Anton

    For example:

    declare @Now datetime = getdate()

    -- 24 hours

    select '1', getdate() - 1, datediff(hour, @Now, getdate() - 1)

    -- 12 hours

    select '.5', getdate() - .5, datediff(hour, @Now, getdate() - .5)

    -- 6 hours

    select '.25', getdate() + .25, datediff(hour, @Now, getdate() + .25)

    -- 144 minutes

    select '.1', getdate() - .1, datediff(minute, @Now, getdate() - .1)

    -- 72 minutes

    select '.05', getdate() - .05, datediff(minute, @Now, getdate() - .05)

    -- 15 minutes

    select '.01', getdate() - .01, datediff(minute, @Now, getdate() - .01)

  • Luis Cazares

    SSC Guru

    Points: 183586

    Yes, it's possible, but I wouldn't advice on using decimals on calculations as it's easy to get it wrong.

    For example, .01 translates into 864 seconds instead of 900 seconds. To get the exact 15 minutes, you need to use something like 0.010416666667.

    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
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717306

    Watch datetime v datetime2 for things as well.

  • aochss

    SSCommitted

    Points: 1677

    Steve Jones - SSC Editor (12/30/2014)


    Watch datetime v datetime2 for things as well.

    As well as the new DATE and TIME types in 2008. Good MSDN blog article on the new types:

    http://blogs.msdn.com/b/cdnsoldevs/archive/2011/06/22/why-you-should-never-use-datetime-again.aspx

    I think the new year will require some time learning the "new" stuff.

    This never gets boring does it?

    Happy New Year everyone,

    Anton

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717306

    aochss (12/30/2014)


    This never gets boring does it?

    Happy New Year everyone,

    Anton

    Nope, and Happy New Year to you

  • Jeff Moden

    SSC Guru

    Points: 995480

    aochss (12/30/2014)


    All,

    I have been programming in T-SQL for a while. I still love what I do since there is still so much to learn. I ran across one of these "Duh, I didn't know that" moments today. You can subtract and add DECIMAL values to a datetime value.

    I do a lot of programming/testing involving batch jobs, data inserts and updates and I need to see the records created/updated in the last day/hour/minutes. I always modified the datetime value in my comparison to get the results until I tried subtracting a decimal value from getdate().

    WOW!!! Do I love this stuff. Hope this helps.

    Anton

    For example:

    declare @Now datetime = getdate()

    -- 24 hours

    select '1', getdate() - 1, datediff(hour, @Now, getdate() - 1)

    -- 12 hours

    select '.5', getdate() - .5, datediff(hour, @Now, getdate() - .5)

    -- 6 hours

    select '.25', getdate() + .25, datediff(hour, @Now, getdate() + .25)

    -- 144 minutes

    select '.1', getdate() - .1, datediff(minute, @Now, getdate() - .1)

    -- 72 minutes

    select '.05', getdate() - .05, datediff(minute, @Now, getdate() - .05)

    -- 15 minutes

    select '.01', getdate() - .01, datediff(minute, @Now, getdate() - .01)

    You can also subtract one DATETIME from another to get duration and then easily format it. See the following article on that.

    http://www.sqlservercentral.com/articles/T-SQL/103343/

    What a lot of folks also miss is that you can add and subtract formatted times as constants instead of jumping through multiple datepart hoops, as well.

    SELECT GETDATE(), GETDATE()+'17:02:23.037'

    ;

    As you've also been previously warned, none of that stuff works with any of the other date/time datatypes except for SMALLDATETIME. Why the monks in Redmond decided to leave such a great capability off the "new" datatypes is anyone's guess.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

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

  • ScottPletcher

    SSC Guru

    Points: 98288

    Jeff Moden (12/30/2014)

    What a lot of folks also miss is that you can add and subtract formatted times as constants instead of jumping through multiple datepart hoops, as well.

    SELECT GETDATE(), GETDATE()+'17:02:23.037'

    ;

    As you've also been previously warned, none of that stuff works with any of the other date/time datatypes except for SMALLDATETIME. Why the monks in Redmond decided to leave such a great capability off the "new" datatypes is anyone's guess.

    I believe datetime handles that just fine; datetime2 does not.

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

  • Jeff Moden

    SSC Guru

    Points: 995480

    ScottPletcher (12/30/2014)


    Jeff Moden (12/30/2014)

    What a lot of folks also miss is that you can add and subtract formatted times as constants instead of jumping through multiple datepart hoops, as well.

    SELECT GETDATE(), GETDATE()+'17:02:23.037'

    ;

    As you've also been previously warned, none of that stuff works with any of the other date/time datatypes except for SMALLDATETIME. Why the monks in Redmond decided to leave such a great capability off the "new" datatypes is anyone's guess.

    I believe datetime handles that just fine; datetime2 does not.

    I didn't make that obvious at all, did I? :blush: I was hitching a ride on the previous post that was talking about it working on DATETIME and didn't explicitly state that. When I said "other", I meant other than DATETIME.

    Yes, absolutely correct. It only works on the SMALLDATETIME and DATETIME datatypes.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

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

  • Gail Shaw

    SSC Guru

    Points: 1004454

    Luis Cazares (12/30/2014)


    Yes, it's possible, but I wouldn't advice on using decimals on calculations as it's easy to get it wrong.

    It's also far less clear than a DATEADD for anyone coming along later and maintaining your code.

    SELECT Getdate()-(1/3.0) vs SELECT DATEADD(hh,-8,getdate())

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ScottPletcher

    SSC Guru

    Points: 98288

    GilaMonster (12/31/2014)


    Luis Cazares (12/30/2014)


    Yes, it's possible, but I wouldn't advice on using decimals on calculations as it's easy to get it wrong.

    It's also far less clear than a DATEADD for anyone coming along later and maintaining your code.

    SELECT Getdate()-(1/3.0) vs SELECT DATEADD(hh,-8,getdate())

    Agreed, but since we're going for clarity as well, why not HOUR instead of "hh"?! I don't get the obsession with using abbrevs for date/time parts, since the full name is usually not that much longer and is 100% clear (Edit: with exceptions for mcs, ms and ns).

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

  • Jeff Moden

    SSC Guru

    Points: 995480

    ScottPletcher (1/2/2015)


    GilaMonster (12/31/2014)


    Luis Cazares (12/30/2014)


    Yes, it's possible, but I wouldn't advice on using decimals on calculations as it's easy to get it wrong.

    It's also far less clear than a DATEADD for anyone coming along later and maintaining your code.

    SELECT Getdate()-(1/3.0) vs SELECT DATEADD(hh,-8,getdate())

    Agreed, but since we're going for clarity as well, why not HOUR instead of "hh"?! I don't get the obsession with using abbrevs for date/time parts, since the full name is usually not that much longer and is 100% clear (Edit: with exceptions for mcs, ms and ns).

    THAT I can answer. I hate Pink! 😉

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

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

  • Gail Shaw

    SSC Guru

    Points: 1004454

    ScottPletcher (1/2/2015)


    GilaMonster (12/31/2014)


    Luis Cazares (12/30/2014)


    Yes, it's possible, but I wouldn't advice on using decimals on calculations as it's easy to get it wrong.

    It's also far less clear than a DATEADD for anyone coming along later and maintaining your code.

    SELECT Getdate()-(1/3.0) vs SELECT DATEADD(hh,-8,getdate())

    Agreed, but since we're going for clarity as well, why not HOUR instead of "hh"?!

    Old habit 😉 , although I only ever use the abbreviations for hour, year and millisecond (and quarter, but I don't recall the last time I needed to DATEADD a quarter). Can never remember which abbreviation is minute or month, so those always get written out in full.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Charles Kincaid

    SSChampion

    Points: 13593

    ScottPletcher (1/2/2015)


    GilaMonster (12/31/2014)


    Luis Cazares (12/30/2014)


    Yes, it's possible, but I wouldn't advice on using decimals on calculations as it's easy to get it wrong.

    It's also far less clear than a DATEADD for anyone coming along later and maintaining your code.

    SELECT Getdate()-(1/3.0) vs SELECT DATEADD(hh,-8,getdate())

    Agreed, but since we're going for clarity as well, why not HOUR instead of "hh"?! I don't get the obsession with using abbrevs for date/time parts, since the full name is usually not that much longer and is 100% clear (Edit: with exceptions for mcs, ms and ns).

    Oh yes. Very clear. If one is speaking English.:w00t:

    ATBCharles Kincaid

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

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