Date range query

[Ruslan Zasukhin]

On 31/8/07 4:30 AM, "Kim Kohen" <kim at webguide.com.au> wrote:

Hi Kim,

> 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'

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'


Kim, why in the second line present SELECT?

You have take it from example of dateadd function, yes?
But that is just an example of call.

The correct call should be as:

where job_details.jd_status = 'Job'
  and job_details.jd_date < 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.

I do not think this is an issue here.

Btw, as I remember exists function to get only DATE part from a DateTime
value.

> Is there a simple way to select from a date range without
> having to parse the time out?

You should not parse time.

Ok, try for now corrected query, and when Ivan will come he can explain
better. DATETIME functions was his development.


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]