SELECT GetDate() – 30 AS [This actually works??];
I just ran across this in a piece of code (that I didn’t write):
… WHERE Thing >GETDATE()- 30
Aaaand it works. If you run the title of this blog post, you’ll get the date 30 days ago. I had no idea you could do this…I’m very much used to using DateAdd() for all my date manipulation needs.
The equivalent DateAdd() syntax, btw, would be
… WHERE Thing > DATEADD(dd,-30,GETDATE())
In a search for “GetDate() – 30″, the first link to come up is a tek-tips.com forum conversation with Denny Cherry (web, Twitter):
dateadd would be a better way to handle this.
dateadd(dd, -30, getdate()). Check BOL for full syntax.…there is little difference between them. However dateadd is a handy function to know in case he later wants to go back by months, or years when getdate()-x doesn’t work.
Denny
Well, that mostly answers that, then. Has anyone seen any performance issues or inconsistent behavior with this particular syntax? I don’t intend to switch, I’m just interested in the variation on the language.
Curiously,
Jen
http://www.MidnightDBA.com/Jen
I have been using GETDATE() +/- ## for as long as I can remember and I have never had any result consistency or performance issues with it – you are still using an in-line function like DATEADD so nothing there.
I would agree with Denny’s point though – every time I do need to use DATEADD for months or years I quick-Google what order the parameters go in! {-:
One problem with using this syntax is it’s not supported by all date data types:
If you have this code:
declare @date Datetime = getdate()
select @date – 1
And you want to change to use datetime2 later you will get errors with this syntax:
declare @date Datetime2 = getdate()
select @date – 1
I still use this short cut often but I try not to put it in production code.
all these work:
SELECT GETDATE()-1
SELECT GETDATE()- 0.0001
SELECT GETDATE()-0.5
but this does not work:
SELECT GETDATE()-(1/5)
the result is GETDATE()
Aaron Bertrand did a pretty extensive post on common date shorthand to watch out for. And I seem to recall reading another post somewhere in line with what Kevin said about syntax similar getdate() – 30 will throw errors in 2012, so probably best to get into the habit of using dateadd(day, -30, getdate() ) for safety.