Date range query

[Ivan Smahin]

Hello Kim,

Friday, August 31, 2007, 4:30:41 AM, you wrote:

> All,

> I'm trying to do a select between the current date and 7 days in  
> advance.  The 'where' part of the query I'm currently using is:

> where job_details.jd_status = 'Job' and job_details.jd_date < (select
> dateadd('31/08/2007', 'day' , 7)) and job_details.jd_date > '31/08/2007'

> This is returning incorrect results - everything after the current  
> date.  I assume this is because dateadd() is returning the time  
> component of the date, the returned value is invalid for a date  
> query. Is there a simple way to select from a date range without  
> having to parse the time out?

dateadd() function returns date-time result always. It is easy to see:

Assume there is a single record - '25.08.2007'

select dateadd( dateField, 'day', 1) from t1

---
26.08.2007 00:00:00:000


The  original  query should select records which date is between now()
and 7 days in advance. So you can do:

select * from t1 where datediff(f1, now(), 'day') > 7

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com