OK, Now a datetime Question

Trying to select from invoices where date is between today and today -30 days

WHERE Invoices.Date<DATEADD(d, -30, GETDATE())

gets no results

And of course, minutes later I get it working with

WHERE Invoices.Date between DATEADD(d, -30, GETDATE())and dateadd (d,0,getdate())

|||Does the invoices.date contains date & time ? Is both date inclusive ?

where invoices.date >= dateadd(day, datediff(day, 0, getdate()), -30)
and invoices.date <= dateadd(day, datediff(day, 0, getdate()), 0)|||

Cammyr:

I think what K H Tan is getting at is that there might be a time of day issue with the where clause:

WHERE Invoices.Date between DATEADD(d, -30, GETDATE())and dateadd (d,0,getdate())

When I select this dateadd function I get a date/time of December 31 2007 10:57AM. If you also want to include in your selection invoices that are between midnight and 10:56AM on December 31 then you need to alter your range -- maybe something like:

WHERE Invoices.Date between convert(datetime, convert (varchar(10), dateadd (d, -30, getdate()), 112))
and dateadd (d,0,getdate())

or something like what K H Tan has suggested.

Dave

|||

your two posts are doing two different things.

the first is older than 30 days and the second is within the last 30 days. if what you desire is not inclusive you could have done...

WHERE Invoices.Date > DATEADD(d, -30, GETDATE())

|||

I just noticed I didn't pay any attention to the other half of the BETWEEN clause.

dateadd (d,0,getdate())

doesn't result in anything different than

getdate()