ansaurus

Question

Answer 1

+2 A:

WHERE posteddate BETWEEN DATEADD(dd, -30, GETDATE())
                     AND GETDATE()

You are aware that GETDATE() includes the time portion?

OMG Ponies 2010-08-06 04:51:43

You could still do WHERE posteddate >= DATEADD(dd, -30, GETDATE()) which reduces the extra call to GETDATE() and be slightly more efficient

Justin King 2010-08-06 05:27:52

How is what you wrote semantically different from the OP's query?

Gabe 2010-08-06 05:31:05

Answer 2

A:

In this scenario you could just do

where posteddate >= getdate()-30

Justin King 2010-08-06 04:53:37

Also in SQL 2008 you could do this cast(GETDATE() - 30 as date) to remove the time as getdate() will return the timeofdayor even in SQL 2005 you could do cast(GETDATE() - 30 as varchar(12)) and that would still do a correct compare from midnight onwards

Justin King 2010-08-06 04:56:30

`getdate` doesn't return a number; it returns a date/time. At that point you have to ask "minus 30 what?"

cHao 2010-08-06 04:57:23

@cHao: Standard SQL says (and SQL Server agrees) that it's "minus 30 days". It *should* work.

Dean Harding 2010-08-06 05:00:02

the - 30 is days. You could use DateAdd if you like but I've never had a problem with this before.If you are unsure what it would return you can always do SELECT Getdate() - 30

Justin King 2010-08-06 05:03:32

@Dean Harding: At first glance, i don't see anything saying that. *Everything* i find says "Use `DATEADD`".

cHao 2010-08-06 05:07:20

@all: Why + and - are bad for date math: http://www.xaprb.com/blog/2006/12/20/why-i-use-explicit-date-math-in-sql/

cHao 2010-08-06 05:10:23

You are talking about MySQL in that link which is totally different to SQL Server. Apples and Oranges. Go with what you feel comfortable with as there are always more than one way to skin a cat.

Justin King 2010-08-06 05:29:52

cHao: I don't know about the SQL standard, but in SQL Server you're adding days. `GETDATE() + 1` is a day from now; `GETDATE() - .25` is 6 hours ago; and so on. I use this all the time.

Gabe 2010-08-06 05:30:21

ansaurus

tags:

views:

answers:

Between .. and clause in sql server?

related questions