CODEWITHSUNDEEP
sqldatetimems-access
AlonP
AlonP

Reputation: 913

SQL - Comparing between Dates in WHERE Statement not work correctly in Access DB

I am trying to compare between two Dates in my SQL statement. The date format I use in my DataBase is DD/MM/YYYY.

When I write the following sql:

SELECT * FROM [MyTableName]  
WHERE #03/10/2014# >= #02/11/2014#;

The result of the WHERE statement is True - (Not Good).
According to the format I wanted (DD/MM/YYYY) it should be False, But in the SQL Statement it appears in a MM/DD/YYYY format.

The same problem was appeared when I used the Now() Function:

WHERE NOW() >= #02/12/2014#;

(Today is the 03/11/2014)
It should be False because 11 < 12 but still the statement is True. (The SQL using the MM/DD/YYYY format).

So I Added a FORMAT function and now it looks like that:

WHERE FORMAT(#03/10/2014#, 'DD/MM/YYYY') >= FORMAT(#02/11/2014#, 'DD/MM/YYYY');

This time the result of the WHERE statement is Flase - (Good!).
So far the Format function looks like a good solution, BUT when trying to reformat the NOW() Date, the problem is appearing again:

This WHERE statement should be True

WHERE FORMAT(NOW(), 'DD/MM/YYYY') = FORMAT(#03/11/2014#, 'DD/MM/YYYY');

But it is False

While this WHERE statement should be False

WHERE FORMAT(NOW(), 'DD/MM/YYYY') = FORMAT(#11/03/2014#, 'DD/MM/YYYY');

And it is True

(Today is the 03/11/2014)

We can see that the Format function not working correctly on the NOW() Date.
Is someone have a solution for that?

Upvotes: 3

Views: 1808

Answers (1)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241890

Date literals, as delimited with # characters, will usually interpret values of the form NN/NN/NNNN in MM/DD/YYYY format - regardless of your culture settings.

In some cases, such as when the first set of digits is > 12, the environment may choose to interpret the value in DD/MM/YYYY order just to handle the input, but this is non-standard behavior that you should not rely upon.

Specifically, MSDN documentation for the VB Date Data Type says the following:

You must enclose a Date literal within number signs (# #). You must specify the date value in the format M/d/yyyy, for example #5/31/1993#. This requirement is independent of your locale and your computer's date and time format settings.

The reason for this restriction is that the meaning of your code should never change depending on the locale in which your application is running. Suppose you hard-code a Date literal of #3/4/1998# and intend it to mean March 4, 1998. In a locale that uses mm/dd/yyyy, 3/4/1998 compiles as you intend. But suppose you deploy your application in many countries. In a locale that uses dd/mm/yyyy, your hard-coded literal would compile to April 3, 1998. In a locale that uses yyyy/mm/dd, the literal would be invalid (April 1998, 0003) and cause a compiler error.

This is true for VB, as well as VBA and MS Access.

One way to work around this is to not use a date literal. Instead, consider using the DateValue function to parse the string to a date object before querying with it.

Upvotes: 2

Related Questions