Date Format Question

J

Jay

Hi,

Is there any way to have Access recognise dates entered in the usual UK
order, namely:

dd/mm/yy

I have tried the different date formats avaible in the format section of the
'General'tab for the Date field's properties (table design view), but the
nearest one 'Short Date' is mm/dd/yy and if dates are entered dd/mm/yy
Access often puts the date as the year, once the data is entered so 31/05/06
becomes 5/6/1931? Any advice would be appreciated as I'm keen not to have
to ask users of the DB to have to put month first when entering dates.

Many thanks,

-Jay-
 
R

Rick Brandt

Jay said:
Hi,

Is there any way to have Access recognise dates entered in the usual
UK order, namely:

dd/mm/yy

I have tried the different date formats avaible in the format section
of the 'General'tab for the Date field's properties (table design
view), but the nearest one 'Short Date' is mm/dd/yy and if dates are
entered dd/mm/yy Access often puts the date as the year, once the
data is entered so 31/05/06 becomes 5/6/1931? Any advice would be
appreciated as I'm keen not to have to ask users of the DB to have to
put month first when entering dates.

Many thanks,

-Jay-
Click to expand...

The "Named" formats ("Short Date", "Long Date", etc.), rely on the Regional
Settings in Windows on each PC. If you want a consistent format across machines
then avoid the named ones and enter an explicit format string like "dd/mm/yy"
(although I would always use a four digit year).

If you allow the user to enter the date any way they like and then rely on the
format property for display consistency (a good practice) then the user's
Regional Settings will be used to translate what they entered. If my Regional
Setting for "Short Date" is mm/dd/yy and I enter a date of xx/xx/xx then Access
will assume that the first pair of digits is the month unless they represent a
number larger than 12. Then it will make other assumptions based on some
internal algorithm. I usually allow Access to make this interpretation and then
use a display format with alpha characters for the month (like May-15-2006) so
the user clearly can see how his entry was interpretted.

It also should be noted that date literals that use numbers only in code or
queries will always be interpretted in US format mm/dd/yy or mm/dd/yyyy. A good
practice is to use ISO format yyyy-mm-dd in those situations.
 
J

Jay

The "Named" formats ("Short Date", "Long Date", etc.), rely on the Regional
Settings in Windows on each PC. If you want a consistent format across
machines
then avoid the named ones and enter an explicit format string like "dd/mm/yy"
(although I would always use a four digit year).

If you allow the user to enter the date any way they like and then rely on the
format property for display consistency (a good practice) then the user's
Regional Settings will be used to translate what they entered. If my Regional
Setting for "Short Date" is mm/dd/yy and I enter a date of xx/xx/xx then
Access
will assume that the first pair of digits is the month unless they represent a
number larger than 12. Then it will make other assumptions based on some
internal algorithm. I usually allow Access to make this interpretation and
then
use a display format with alpha characters for the month (like May-15-2006) so
the user clearly can see how his entry was interpretted.

It also should be noted that date literals that use numbers only in code or
queries will always be interpretted in US format mm/dd/yy or mm/dd/yyyy. A
good
practice is to use ISO format yyyy-mm-dd in those situations.
Click to expand...

Thanks for the comprehensive answer Rick. I really appreciate you taking the
time. Your answer also kinda sheds light on what appeared to be an anomaly:
With the date field set as the named "Short Date" there appeared to be
inconsistencies. For example if 12/05/06 (meaning 12th May) is entered into
the field then Access leaves it in that order. However, if 31/05/06 is
entered then Access translates this to 5/6/1931 ? So I guess this
apparently inconsistent behaviour is expalined by the internal algorithm.

Thanks again,

-Jay-
 
P

Pat Garard

G'Day Jay,

One SMALL point......

The format for date data used in SQL or in code is mandated by the SQL
standard....and happens to be "mm/dd/yy" which is also the US format.

Your are never using Access - always a marriage of Access and SQL.

The "Access" partner will try to comply with regional settings until it passes
dates to SQL.

In the UK (and Australia) if you (for example) pass parameters to a Query,
Access will reverse day/month to month/day if it can, and if it can't it wont -
but it never tells you whether it did or did not!

When you apply date criteria to a Query this can lead to results that be
amusing, ridiculous or disastrous depending on your perspective.
 
D

Douglas J. Steele

Not quite, Pat.

Access is quite happy in SQL statements to use any unambiguous format, such
as yyyy-mm-dd or dd mmm yyyy. When the date is presented in nn/nn/nnnn
format, though, it totally ignores the Short Date format that's been set
through Regional Settings, and will always try mm/dd/yyyy first. Only if
that doesn't succeed (because it's after the 12th day of the month) will it
accept the input as being in dd/mm/yyyy format.

This is discussed in some detail in the articles by Allen Browne & myself
mentioned earlier in this thread.
 
P

Pat Garard

Thank you Douglas,

The points I have failed to make:
Access will reverse day/month to month/day if it can (sensibly)
of course there must be ambiguity for this to occur.
It never tells you whether it did or did not,
there is a consistency black hole.
This is especially relevant in non-USA language formats
where this ambiguity is the norm.
The standard is not English (US), but SQL.
 
J

jarl

Pat said:
The format for date data used in SQL or in code is mandated by the SQL
standard....and happens to be "mm/dd/yy" which is also the US format.
Click to expand...

Hi Pat,

The ANSI/ISO SQL standard specifies the date format as YYYY-MM-DD. The
MS database products are not very standard compliant when it comes to
datetime handling.


/Jarl
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Noob short format date question 1
Excel Macro to copy a row to another sheet based on date 0
How to enter a date in DD-MM-YYYY format 2
Date Format 2
date format 1
Forcing a date entry format of dd/mm/yyyy 1
Date Format Problem. 2
Help with Date Format 4

Top