Home » sql server »sqlserverpedia-syndication »SSC »TSQL » Currently Reading:

SELECT GetDate() – 30 AS [This actually works??];

December 19, 2011 sql server, sqlserverpedia-syndication, SSC, TSQL 4 Comments

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

Currently there are "4 comments" on this Article:

  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. Kevin says:

    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. leo says:

    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. Chuck Rummel says:

    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.

Comment on this Article:







Release Date: Minion by MidnightDBA

Just 9 hours 7 minutes 12 seconds until we release Minion Redindex: index maintenance done right!

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

November 3-7: PASS Summit, Seattle, WA

PASS Summit: Jen is presenting How to Interview a DBA: A Panel Debate on Thursday 11/6 1:30pm, room 401 (along with Adam Machanic, Sean McCown, Bob Pusateri, and Michelle Ufford).

PASS Summit: Sean is presenting Performance Tuning Your Backups on Wednesday 11/5 3:00pm, room 602-604.

December 11: Presenting "Powershell Cmdlets.." at Alaska SQL Server User Group

January 30: "Become an Enterprise DBA" precon at Austin SQL Saturday

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/