Problem with date day/month reversing on save

Question

We have a problem affecting the production environment only.

We have a VB6/ASP website that allows for data in a database table to be hand edited.

It looks alot like an editable datagrid.

One of the editable columns is a date and when the record is saved dates day/month are being reversed.

03/11/2008 becomes 11/03/2008, if you were to resave the record the date is again 03/11/2008.

I have checked the DB value and it is indeed being reversed however the same identical code on the test systems does not do this.

So i'm very confident i'm looking for an environment change. The test system has the DB and Site hosted locally and in the live setup we have a separate web site server and database server. Where do you suggest I start looking for this problem. I've checked the regional settings on the servers and they are set to United Kingdom and the OS date format is correct.

This is SQL Server 2000. hit me with some ideas!.

Thanks :)

Answer 1

How about the regional settings of the OS?

Answer 2

This is almost certainly to do with either your machine or the db server being set to US date format. Double check both the system settings.

Alternatively, a quick Google search will bring up a few options for manipulating the data so it will do what you need.

Answer 3

If you convert the user supplied string to a date before you feed it to the DB, then the Session.LCID of the thread executing your ASP page is responsible for how dates are interpreted.

If you feed the date as a string to SQL Server and let the conversion happen there, it will be useful to look into the SET DATEFORMAT and the SET LANGUAGE statements.

So here is my idea: Either stop using (encouraging your users to use) ambiguous date formats, and this problem will disappear, or make sure all links of the processing chain have a clear understanding on what format to expect.

Answer 4

The following

select name ,alias, dateformat from syslanguages where langid = (select value from master..sysconfigures where comment = 'default language')

Returns the same result on test and live

us_english English mdy

The problem is the system cannot be redeployed to changed. I need to identify the cause and fix it.

Answer 5

Go into regedit and go to the following location in the Windows Registry:

HKEY_USERS.DEFAULT\Control Panel\International\

Check to make sure that the "sShortDate" field is correct.

The short date format stored in this location many times is different then the short date format stored in the Windows Control Panel/Regional Options. The short date in the regional options is for the user that you are logged into Windows as. The "sShortDate" field in HKEY_USERS in the short date format used by the Windows Services.

Answer 6

I have the same problem, this problem is only updateing dates, it switches the month with the day when the day is lower or same as 12. I even use an string to insert the date, i checked the sql command before the execute it but its totally fine. the time setting are ok in my computer too, so what can it be the problem?