UK Dates format keeps changing to US format

[peterdelve]

I have a database with a number of date fields every single one has been specified to (short date format) and are on machines with UK dates format dd/mm/yyyy. However after using the database for a few weeks the dates change to US format. For no particular reason and I cannot trace it to anything.

I have changed all of the code support the forms to Date() instead of Now() but this has made no diffrenece as users are still reporting the dates have changed to us format.

The database is running on a standard NT server with the desktops using windows NT. Users cannot access/amend the date format, therefore it is always english. But I cannot determine why thiis happening, and it is happending to all users.

Any help would be appreciated

Pete

 

[adam_fleck]

Use Number, Double instead of Date/Time?

I had this problem too, and couldn't get to the bottom of it. I came up with the probably not very good theory that even on a network where European settings have seemingly been imposed across the board, server Access sometimes overcorrects.

If local Access posts a dateSerial to a date/time field on the server, the server field will sometimes assume the local machine has sent the dateserial of a US-style date string by mistake. It tries to undo this by switching the day and month around if this will result in a valid date. I couldn't find out how to stop this, so I used an ugly fix.

Ugly Fix Part 1

I changed all the timestamp fields in the database from Date/Time to Number, Double, and set the "dd/mm/yyyy" format when displaying values to users.

Ugly Fix Part 2

'dbNow (in a module) makes a date serial.
'dbNow(0) gives only the date,
'dbNow(1) gives the time too.

Public function dbNow(intIncludeTime as Integer) as Double
	
    dbNow = DateSerial(Year(Now), Month(Now), Day(Now))

    If intIncludeTime = 1 then
        dbNow = dbNow + TimeSerial(Hour(Now), Minute(Now), Second(Now)
    End If

End function

Comment

On the theory of over-correction, dbNow probably isn't necessary, but I reckoned I didn't have time to be scientific, and since the ugly fix worked I left it alone. I've since moved to SQL server, which handles dates, oh joy, in a slightly different way ( counting from 01/01/1753 or from 01/01/1900, depending on the date type you choose). SQL server, though, is much more reliable.

Mile-o-Phile runs a history forum, and might be able to tell us whether 1753 was a significant year.

But maybe this is it:

"January 1 1753 - Britain and its colonies adopt the idea that January 1st should be New Year's Day, following adoption of the Gregorian calendar in September 1752. The concept was first conceived in 1582, but suffered from slow public adoption."
http://en.wikipedia.org/wiki/1753

European/US date conversion can be annoying, but things could have been much worse, as explained at http://en.wikipedia.org/wiki/Gregorian_calendar.
http://en.wikipedia.org/wiki/Calendar_date is interesting too.

 

[Jon K]

.......... on machines with UK dates format dd/mm/yyyy. However after using the database for a few weeks the dates change to US format. For no particular reason ........

Did you use the # signs to delimit dates? Access will read dates delimited by the # signs as in the US date format if both the day and month are less than 13 e.g. #07/02/2004# is July 2nd, 2004.

On machines with UK dates, you can use the DateValue() function instead of the # signs.

See Pat's thread "Working with non-US date formats":-
http://www.access-programmers.co.uk/forums/showthread.php?t=63221&highlight=DateValue
.