How best to compare Dates?

[essaytee]

My regional setting for Dates is set to "dd/mm/yyyy". Below are some results from my Immediate Window.

? Day(#30/11/2018#)
 30 
? Month(#30/11/2018#)
 11 
? Year(#30/11/2018#)
   2018

The above shows that the Day, Month & Year are correctly extracted from the inputted date.

? #30/11/2018# > #29/11/2018#
 True
? #1/12/2018# < #2/12/2018#
True

The above comparisons correctly return the right results. Not so for the following.

? #30/11/2018# < #1/12/2018#
 False
 
? #1/12/2018# < #30/11/2018#
True

As can be seen, 30 Nov is in fact before (or less than) 1 Dec, and yet the result is wrong. Likewise, switching the dates around also yeilds an incorrect result.


What is the best way to compare dates?

Further, why is the following returning a correct result?

? Date()
 30/11/2018 
? Date()+1
1/12/2018 

 ? Date() < Date()+1
True

So the above comparison 30 Nov < 1 Dec returns the correct result.

 

[pbaldy]

I'll let somebody more experienced with international dates chime in, but what do you get with

?Day(#1/12/2018# )

Your tests involve dates that can only be interpreted one way and dates that can be interpreted either way.

 

[Galaxiom]

Regardless of the regional date settings, once the date delimiter is used, the default interpretation is mm/dd/yyyy.

However, when confronted with a date a nonconforming date, Windows (Access uses a Windows API) will attempt to interpret it in any way that returns a viable date. This happens both when casting a text date or interpreting a delimited date.

My favourites are dates like 29/2/03 which is interpreted as 3 Feb 1929. It is only valid as yy/mm/dd so Windows quietly accepts it as such.

 

[isladogs]

I also use dd/mm/yyyy here in the UK.
However for SQL comparisons to work reliably you must use either mm/dd/yyyy or yyyy-mm-dd

Access causes confusion by trying to be helpful.
Where dates are unambiguous like 13/10/2018, Access will read them correctly as 13 Oct.
However where the first part of the date 'string' is between 1 & 12, Access will treat it as the month.

So 30/11 is after 1/12 (12 Jan)

But if you ask Access to determine the day after 30 Nov, it knows it's 1 Dec and displays it in your regional date format.

You should be able to work out all the results from that

 

[isladogs]

My favourites are dates like 29/2/03 which is interpreted as 3 Feb 1929. It is only valid as yy/mm/dd so Windows quietly accepts it as such.

Actually now we're in the 21st century...

?#29/2/03#
03/02/2029

Same idea though

 

[essaytee]

Thanks all for responding, yes it's all down to Access interpreting the date incorrectly. Interestingly though,

? Day(#1/12/2018#)
 12 
? day("1/12/18")
   1

Obviously, the string version is adhering to my regional settings.

What is the recommended approach to passing a date into a query, for that date to be compared to fields of type date/time?

Should the date value be a type string?
Should the date, in string form, be "mm/dd/yyyy"?

 

[essaytee]

Answering my own question. Simple function created that converts my date value to a string value, mm/dd/yyyy, and all works. I now recall this issue/problem from many years ago.

 

[Galaxiom]

Thanks all for responding, yes it's all down to Access interpreting the date incorrectly.

Not actually Access but Windows. This is also the case in Excel, VB.NET, ASP etc.

Obviously, the string version is adhering to my regional settings.

Only because it is a valid date in your regional settings. It will quietly accept US or ISO format if it not valid in your region but valid in one of the others.

What is the recommended approach to passing a date into a query, for that date to be compared to fields of type date/time?

For an SQL command text, everything is really a string. Always use the US format because that is SQL native format.

Best use the date selector if entering dates on a form. Otherwise wrong values will occasionally (or regularly ) be typed in by operators.

 

[theDBguy]

Hi,

Pardon me for jumping in, but just in case you haven't seen this.

International Dates in Access

 

[essaytee]

Hi,

Pardon me for jumping in, but just in case you haven't seen this.

International Dates in Access

Thanks, a good read.