Can't get my date query to return expected values

[johncassell]

Hi There,

I have 2 jobs in my database with the following start dates:

17/09/2007 00:00:00
17/09/2007 23:59:59

I have a query with the following code:

SELECT Job_Number, Start_Date
FROM         WS_Jobs
WHERE     (Start_Date BETWEEN @Param1 AND @Param2)

Problem is if I have the following parameters:

Param1 = 17/09/2007
Param2 = 17/09/2007

It only returns the second job (With time of 23:59:59)

How can I get it to recognise that 17/09/2007 00:00:00 is in the range?

Even if I have the following parameters, it still doesn't show that job;
Param1 = 16/09/2007 23:59:59
Param2 = 17/09/2007 23:59:59

Anyone have any ideas?

Thanks

John

 

[Arg81]

I think this has been mentioned before on other threads.

Not sure if this was the answer, but use something like

WHERE (Start_Date >= @Param1) and (Start_Date <= @Param2)

you can then change whether you need the "equal to" part or not...this is how I do my date queries and everything seems to be OK with mine...

 

[cjard]

How can I get it to recognise that 17/09/2007 00:00:00 is in the range?

Use an operator that is inclusive. BETWEEN is exclusive. If I asked you for all the numbers BETWEEN 1 and 5, you should say to me "2, 3, 4"

Even if I have the following parameters, it still doesn't show that job;
Param1 = 16/09/2007 23:59:59
Param2 = 17/09/2007 23:59:59

It ought to show the first, but not the second, with these params

Anyone have any ideas?

Never use BETWEEN for dates. Use proper operators that are not ambiguous in meaning like arg81 advises

 

[cjard]

you can then change whether you need the "equal to" part or not...this is how I do my date queries and everything seems to be OK with mine...

In our business, we define date ranges as

start <= x < end

this ensures no overlap for dates that fall on the boundaries

 

[johncassell]

thanks a lot for the help fellas,much appreciated.

My query is working great now.

cheers

John