# 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