SELECT dates BETWEEN problem??

[danb]

Hi, this is driving me crazy.

I have an Access table containing a field called "DateOfInput". This is set as Date/Time data type with a default value of 'Date()'.

When I insert a new record into the table, the date feild is automatically filled with the following example data - in dd/mm/yyyy format:

29/09/2004
01/10/2004
15/10/2004
16/10/2004

This is fine. I'm based in the UK so this is how I want the data (dd/mm/yyyy).


However, when I use a T-SQL select (in ASP) to select data from the table - I get very inconsistent results:

SELECT * FROM tblRegistrant WHERE DateOfInput BETWEEN #09/10/2004# AND #16/10/2004#.

This should return only two of the records from the above set of dates, but instead it returns all??

If I do this:

SELECT * FROM tblRegistrant WHERE DateInput BETWEEN #14/10/2004# AND #16/10/2004#

It does return the correct data???

Could this be anything to do with the data being in UK format (as opposed to mm/dd/yyyy)? If so, then how do I change this? The auto-populated date in Access is UK format.

Any help would be great thanks...

 

[panaseam]

Hi danb

Its not your data that's the problem, the fact that you see the data in
UK format doesn't alter how the data is actually stored.

The problem is in the SQL engine.
The date #09/10/2004# is interpreted as US format which in UK terms would be #10/09/2004#
whereas the date #16/10/2004# is automatically understood by the SQL engine because in US format it can't exist so it recognises the UK format date correctly.

The only safe way is to always format your SQL dates in US format.

So your query should have read
SELECT * FROM tblRegistrant WHERE DateOfInput BETWEEN #10/09/2004# AND #10/16/2004#.

Hope this helps.

 

[danb]

Thanks very much panaseam, I do belive that's sorted me out. Much appreciated.

 

[panaseam]

You are absolutely right Uncle Gizmo!

I hate to think how much time I've wasted messing about with dates.