Date formats mismatch between different servers (XML column)

Question

Hi,

I am facing a problem for past two days with regard to the date format in SQL.

I have developed a application in ASP.NET and have deployed it in production server. I am persisting data into a table which has an xml column with date feilds like

---some date time---

When I run the application from localhost ( or deploy the application in my computer) the date is persisted in in 24 hours time format

For eg :15/08/2009 14:30:00

and when I run the same application deployed in the production server the data is persisted in 12 hours time format

For eg: 15/08/2009 2:30:00 PM

Note: I am not specifying any format in the application code when persisting data.

The following is what I have tried :

1. Checked regional settings of my computer and the webserver and format is the same (h:mm:ss tt)

2. Have checked the culture info of both my computer and webserver and it is the same. (en-AU)

3. In my computer's IIS Properties I have set the same culture of the webserver by Editing the configuration.

4. The dates in date columns of the same table are stored in consisntent format (24 hr format) when persisted from both my computer and the production webserver.

For eg: 2009-10-28 14:00:49.000

Still the date time is stored in different formats. I am unable to figure out from where the application is taking the format when persisting.

Kindly provide a solution for this.

Thanks in Advance.

Regards, Ramya

Answer 1

Try add the following to the web.config of your website:

<system.web>
<globalization culture="en-ZA" uiCulture="en-ZA"/>
</system.web>

Replace "en-ZA" with the specific culture you require. The will ensure that the browsers culture settings will not be used.

Answer 2

Hi,

Already globalization culture="en-AU" uiCulture="en-AU"> is present in web.config just now verified. Still doesnt work.

Thanks.

Answer 3

Hi,

I finally found the root cause for this problem. Thanks to my friend Gopi,who helped me on this. I would like to post it just in case someone faces this same problem this might help them.

Go to regedit. Select Controlpanel -> international -> sTimeFormat and verify the time format given.

In my computer, the time format is HH:mm:ss and in server it is hh:mm:ss tt that was the main problem. I have changed the format in server to HH:mm:ss and it works perfect :)

Please refer this link for detailed info

http://www.eggheadcafe.com/conversation.aspx?messageid=33829074&threadid=33829058

Thanks to Stackoverflow for your immediate response.