How many different places do you have to set date

Question

I am trying to migrate an old classic asp application to IIS 7.0 with win2k8 and I definately don't want to change the old code if I can help it.

I have set a date format in the Customise Regional Options in the Date formats in the regional and and language options in the control panel to dd-MMM-yy.

I am still getting Conversion_failed_when_converting_datetime_from_character_string errors using this date format even after reboots.

I am using also using SQL Server Express 2005.

Where else do you need to set date formats? Should they all be picked up from the machine setting?

The old code does something really dumb, but again I don't want to have to change it and then test it all again.

The old code effectively does this "Insert Into MyTable (ThisDate) Values ('" & Now() & "')"

Generates Insert Into MyTable (ThisDate) Values ('17/03/2009 3:40:48 p.m.')

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting datetime from character string.

What it's ACTUALLY complaining about is the "p.m." part of the string dispite the Region settings on the machine specifying to NOT use this.

Answer 1

This is a very annoying problem which comes from installing Server 2008 with a different region setting to what you ultimately want to use.

Typically this problem arises where the Server is set to USA and the user then subsequently sets the location to UK. Then when the code is executed it returns 1/17/2012 instead of 17/1/2012 and yes SQL Server may return errors like "Conversion_failed_when_converting_datetime_from_character_string".

The problem is that the IUSR account has its own region setting and it's this setting that the server uses when executing classic ASP. This is what you have to do to fix it...

Open the Region and Language options from the Control Panel. You should see the "Administrative" tab, click on Copy Settings. Make sure that the settings listed under 'Current User' are those that you want replicated, if they're not then backtrack to the keyboard layout etc and set them. Tick the box 'Welcome screen and system accounts' and then OK. Reboot.

Now it should work.

Answer 2

There is only one place you have to set it, for any application that pays attention to regional settings, and you've already set it there.

What is "Conversion_failed_when_converting_datetime_from_character_string" from? VBScript? Maybe it doesn't pay attention to regional settings.

Answer 3

All you have to do is change the culture setting in the .Net Globalization section to your country and it should fix it.