Comparing dates in SQL statement

[emozley]

Hi,

When I execute the following command on my table in Access:

SELECT Passwords.Email, PassWords.LastUpdated, Users.UserID, Users.FirstName
FROM Passwords LEFT JOIN Users ON Passwords.UserID=Users.UserID
WHERE LastUpdated<#7/5/2005#;

It returns

e.mozley@myaddress.co.uk 07/06/2005 124 Ed

Why is this?

Surely 07/06/2005 is not less than 07/05/2005 and so no row should be returned?

Any help much appreciated.

Thanks

Ed

 

[PHV]

What is the data type of LastUpdated in the Passwords table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[emozley]

Last Updated is Date/Time and the Format is set to Short Date.

 

[Golom]

Access internally uses mm/dd/yyyy when converting and, by that convention, #7/5/2005# is July 5, 2005. The returned value is however reported in your locale short date format which I gather is dd/mm/yyyy so 07/06/2005 is May 6, 2005 which is before July 5. Use some non-ambiguous format like

WHERE LastUpdated<#7/May/2005#;

 

[PHV]

Or the ANSI-Like format:
WHERE LastUpdated<#2005-05-07#;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[emozley]

I see! So even though the date is stored as dd/mm/yyyy when Access does the maths it is expecting it to be stored mm/dd/yyyy.

Did what you suggested - fortunately with ASP it is very easy to extract and re-arrange the bits you want.

Thanks very much

Ed

 

[PHV]

even though the date is stored as dd/mm/yyyy
A date is stored as a floating point number.
The integral part is the number of days since 1899-12-30
and the decimal part is the time of the day (in seconds I think).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886