date format display

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lynn atkinson - 27 Jul 2005 14:57 GMT

I have a database holding information about our employees including some date
fields. We have had problems with our recruitment administrator's profile in
that when she put in dates, the system changed the display of dates from UK
to US format, so instead of someone starting on 9/6/05 (9th June 05) the date
is displayed as 6/9/05 (US format). I assumed that dates were stored as
numbers and when this profile problem was fixed the dates would revert to the
UK format as input. Hoewver, this is not the case and now we have a database
in which the displayed dates cannot be trusted.
Is there a way of converting these dates to a serial number then to the UK
format or any other way of ensuring the dates we are viewing are those in put
in UK format?

Any help would be appreciated.

Reply to this Message

Klatuu - 27 Jul 2005 15:08 GMT

Date display is determined by the Regional settings on your computer. Go to
Control Panel -> Regional and Language Settings, and set the date display
however you want it.

> I have a database holding information about our employees including some date
> fields. We have had problems with our recruitment administrator's profile in
[quoted text clipped - 9 lines]
>
> Any help would be appreciated.

Reply to this Message

Allen Browne - 27 Jul 2005 15:10 GMT

Internally, Access stores date/time fields as a number, where the integer
part represents the date, and the fraction part the time of day (noon = .5,
6am = one quarter of a day, and so on.)

That means that there is no formatting within the stored date at all. It is
applied at interface time. Therefore there is no way to know which dates
were entered via a particular format.

If you know which records need to be fixed, it would be possible to use an
Update query to spin them around. Use Year(), Month(), and Day() to parse
them, and DateSerial() to create them the other way around. For example, if
the field were named "dt", you could spin it around like this:
DateSerial(Year([dt]), Day([dt]), Month([dt]))

For more info on how Access interprets dates and how to achieve an interface
that works consistently in different regions, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a database holding information about our employees including some
>date
[quoted text clipped - 16 lines]
>
> Any help would be appreciated.

Reply to this Message

lynn atkinson - 27 Jul 2005 16:07 GMT

Thanks.
I have misunderstood how the numbers work. I thought that, for example 30
June 05 would be stored as number 87654321, so if you applied a US format to
this it would appear as 6/30/05; if you applied a UK format it would appear
30/6/05. But if this is not the case, then we are stuck. The data input was
an intermittent/random problem, sometimes the dates kept the UK formatting,
other times it changed, so we will have to backtrack with the paper to
correct.

thanks for you reply

> Internally, Access stores date/time fields as a number, where the integer
> part represents the date, and the fraction part the time of day (noon = .5,
[quoted text clipped - 36 lines]
> >
> > Any help would be appreciated.

Reply to this Message

Rick Brandt - 27 Jul 2005 17:46 GMT

> Thanks.
> I have misunderstood how the numbers work. I thought that, for
> example 30 June 05 would be stored as number 87654321, so if you
> applied a US format to this it would appear as 6/30/05; if you
> applied a UK format it would appear 30/6/05. But if this is not the
> case, then we are stuck. \

Actually 30 June 2005 is stored as 38533, but otherwise that _is_ the case.
Read Allen's reply again. As far as storage is concerned a date is a date
is a date. Formatting is completely irrelevant until you display it.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message