Dealing with UK dates

[AB1984]

Hi All,

I'm about a month into the work on designing a database, and up until now I've managed to avoid this issue, but it's suddenly reared its ugly head. Currently any dates that can be changed are changed (e.g. selecting 4th july 2017 in date picker results in 07/04/2017 being displayed and treated as 7th April), if the fist character is higher than 12 though, the date is handled as I want.

Unfortunately a lot of my code is built around treating all days in UK format, or ISO YYYY-MM-DD format. But I'm thinking of overhauling all the dates throughout my code in order to get some consistency. However, I need all my tables and form controls to display and handle dates in DD/MM/YYYY format as if they are DD/MM/YYYY format.

Does anyone have any advice on the steps I should take to get everything working smoothly, and good practice moving forwards from these changes?



Any advice would be greatly appreciated!

Cheers

[ranman256]

all dates , no matter the country, are stored the same,
numerically.
So for users: in forms, or queries, you can set the textbox format to display YOUR format.
Be sure all edit access points are formatted to your specs.

[AB1984]

Hi Ranman. Thanks for the response, much appreciated.
If you could just provide a little clarity:

When you say dates are stored numerically, I found somewhere it's the count of days (and fractions of days after the decimal point) since midnight Dec 30th 1899, is that what you mean? And how would I go about using this numeric value in code?

And what are edit access points? sorry if that sounds a bit dim, do you mean the formats in the design view of tables?

Thanks!

[CJ_London]

a date datatype is stored as a form of double datatype (the same goes for Excel or any other app) with special formatting characteristics.

the bit before the dp is the count of days from 31/12/1899. The bit after the dp is the time expressed as the number of seconds so far today divided by the number of seconds in a day (86400) - so 6am would be 0.25

The format is just how you see the date - normally set in the windows settings for the country - and is effectively a string

Comparing dates is straight forward - date1=date2

but if you are comparing different datatypes - for example a date field in a table compared with 07/04/2017 entered in a form (which is a string) - then you need to convert one of them to the same datatype as the other.

you use the # character to surround a string which tells Access that the values between the # character is to be treated as a date. However the query engine that does this assumes the format of the string is in the US format of mm/dd/yyyy. But when there is an 'error' e.g. the date is 31/12/2016, it knows there are only 12 months in the year and swaps the day and month round. However if there is ambiguity (04/07/2017) then it will assume the US format.

There are a number of ways to get round this

1. force the date to be formatted in US format (the usual method) - WHERE date="#" & format([Enter Date],"mm/dd/yyyy") & "#
2. use an unambiguous format of the date - in your control set the format property as dd mmm yyyy or in the query - WHERE date="#" & format([Enter Date],"dd mmm yyyy") & "#"
3. use the Cdate function which converts text to a date based on the local settings - WHERE date=Cdate([Enter Date])
4. convert the value in the table to a string - WHERE Format(date),"dd/mm/yyyy")=[Enter Date]

Do not confuse the format function with the format property. The function converts a value to a string, the property only displays it as a string.

If you convert a date to a string then the sort would be
01/02/2016
01/03/2016
02/02/2016

but using the property it will sort correctly on the underlying value
01/02/2016
02/02/2016
01/03/2016

also be aware of the Date and Now functions and the potential for criteria producing the wrong result. Date has a time value of 0 which means it is midnight at the start of the day. so a criteria of

myDateTime<=Date

will not return anything when Date is 31/03/2017 and myDateTime is 31/03/2017 08:00:00

A common mistake where users have used now() as a default rather than date()

[Micron]

my 2 cents regarding the example of a date comparison using the <= operators:
The same effect can be experienced (perhaps unknowingly) when using the BETWEEN query operator with 2 dates as criteria when those fields contain the time component of the date. WHERE aDate BETWEEN DateTime1 AND DateTime2 will have the same result as what you outlined. My solution is usually to use the DateAdd function to increase the second date by some number of minutes or seconds, bringing the second date up to just before midnight on the second date. The alternative is to use >= with < where the second date is the date desired plus one day. The issue for me as been how to apprise the users that they need to add an extra day to their target date, or simply do it for them in the hopes they don't add another one because they're aware of the issue. I opted for the date add function since only the experts in the work order management system were aware of the anomaly; the common db user was not.

[CJ_London]

I agree - all depends on circumstances. Another way is to use the datevalue function on the value with a time element

[HiTechCoach]

This may help:

International Dates in Access