SQL - Date comparison in WHERE clause does not work correctly in Access DB
I am trying to compare two Dates in my SQL statement. The date format I am using 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 clause True
is (not good).
According to the format I wanted ( DD/MM/YYYY
) it should be False, but in the SQL Statement it is displayed in the format MM/DD/YYYY
.
The same problem came up when I used Now()
Function:
WHERE NOW() >= #02/12/2014#;
(Today - 03/11/2014)
It should be False
because 11 < 12
, but still the statement is true. (SQL using format MM/DD/YYYY
).
So, I added a function FORMAT
and now it looks like this:
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 a NOW () date, the problem reappears:
This WHERE clause must be True
WHERE FORMAT(NOW(), 'DD/MM/YYYY') = FORMAT(#03/11/2014#, 'DD/MM/YYYY');
But this False
While this WHERE clause should be False
WHERE FORMAT(NOW(), 'DD/MM/YYYY') = FORMAT(#11/03/2014#, 'DD/MM/YYYY');
And this True
(Today - 03/11/2014)
We can see that the Format function does not work correctly on the NOW () date.
Does anyone have a solution for this?
source to share
Date literals, in the form of characters #
, usually interpret form values NN/NN/NNNN
in the format MM/DD/YYYY
- regardless of your culture setting.
In some cases, such as when the first set of digits is> 12, the environment may choose to interpret the value in order DD/MM/YYYY
only to process input, but this is non-standard behavior that you should not rely on.
Specifically, the MSDN documentation for the VB date data type states the following:
You must enclose the date literal with numeric characters (# #). You must specify the date value in M / d / yyyy format, for example # 5/31/1993 #. This requirement is independent of your language settings and your computer's date and time format settings.
The reason for this limitation is that the meaning of your code should never change depending on the language in which your application is running. Suppose you are strictly coding the date literal # 3/4/1998 # and assume that it means March 4, 1998. In locale using mm / dd / yyyy, compile 3/4/1998 as you plan. But let's say you are deploying your application to many countries. In a locale using dd / mm / yyyy, your hardcoded literal will compile until April 3, 1998. In a locale that uses yyyy / mm / dd, the literal will be invalid (April 1998, 0003) and will cause a compiler error.
This is true for VB as well as VBA and MS Access.
One way to get around this is to not use a date literal. Instead, consider using a function DateValue
to parse the string before the date object before querying it.
source to share