date format display

[Guest]

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.

 

[Guest]

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.

 

[Allen Browne]

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

 

[Guest]

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,
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Rick Brandt]

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.