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

Question

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

aochss

SSCommitted

Points: 1677
More actions
December 30, 2014 at 8:35 am

#293869

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)

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

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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: 734449 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 734449

December 30, 2014 at 9:34 am

#1767030

Watch datetime v datetime2 for things as well.

aochss SSCommitted Points: 1677 More actions · Answer 3

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: 734449 More actions · Answer 4

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: 1003863 More actions · Answer 5

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.

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: 100948 More actions · Answer 6

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

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.

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: 1004504 More actions · Answer 8

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: 100948 More actions · Answer 9

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) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

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.

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: 1004504 More actions · Answer 11

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 More actions · Answer 12

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