Access and ASP date format problem 3

[beaniebear]

I am entering dates into Access 2000 via an asp form. The dates are being entered in the format (dd/mm/yyyy). I have displayed the sql on screen so i know this is fine. When the record is inserted into access the date format is changed to mm/dd/yyyy. Also if I run any select queries from asp they use the date format of dd/mm/yyyy.

If i enter dates directly into the db they show in the format dd/mm/yyyy. The regional settings are set to dd/mm/yyyy.

This now sending me nuts! Please help!

 

[Golom]

A well-known characteristic (bug?) of Access is that it does not use locale settings for dates of the form "03/05/2004" (for example) when it is converting a date on input. Access's internal date format is "mm/dd/yyyy". If you input a date that is ambiguous then it will use "mm/dd/yyyy" for conversion.

Dates converted on output do use the locale settings (dd/mm/yyyy in your case.)

You need to input dates in an unambiguous format such as

02/Apr/2004 or

DateSerial ( yyyy, mm, dd )

 

[lupins46]

If you are using an SQL statement to add to the table then you must specify an unambiguous data format in your sql string.
Use format(datefield, "yyyy-mm-dd")

 

[jgoodman00]

Date formatting issues are a nightmare & I wish we could just globally agree to a single format & stick with it.

However, I got round this issue by specifying a server locale in the global.asa file for the asp application:

Sub Session_OnStart

     'Set the server locale
     Session.LCID = 2057

End Sub

I have not had any problems since...

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[PHV]

we could just globally agree to a single format
Use the ANSI format: 'yyyy-mm-dd hh:nn:ss'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beaniebear]

Thanks very much for all your responses. This has been driving me mad!