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.

 

[Uncle Gizmo]

International Date Format

I would like to see a move toward's using the international date format for dates used in software. For those who don't know, the international date format is:

2004 10 17

or in words:- Year, Month, Day.

Programmers would know to use this format if it became a standard and it would immediately eliminate the problems caused between using U.S. and UK dates.

One major advantage of this format:-

I use this date format all the time when I save files for the simple reason that if you save a (file name with the date included) in this format you automatically get a list of files that sort by date, ascending or descending.

2004_10_17_Stock.xls
2004_10_16_Stock.xls
2004_10_14_Stock.xls
2004_10_10_Stock.xls
2004_10_09_Stock.xls
2004_09_17_Stock.xls
2004_09_16_Stock.xls
2004_09_14_Stock.xls
2004_09_10_Stock.xls
2004_09_09_Stock.xls
2003_10_14_Stock.xls
2003_10_10_Stock.xls
2003_10_09_Stock.xls
2003_09_17_Stock.xls
2003_09_16_Stock.xls

 

[panaseam]

You are absolutely right Uncle Gizmo!

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