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

4 thoughts on “SELECT GetDate() – 30 AS [This actually works??];”

  1. 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! {-:

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

  3. 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()

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>