datetime format setting --> mm-dd-yyyy instead of dd-MM-yyyy in SQL Server 2005 / expre(is it

I’m getting a datetime format problem(mm-dd-yyyy for dd-MM-yyyy), when I install SQL Server 2005 Express. {The exception is: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.}

My windows Regional and Language options – English (United Kingdom), Sort date format is dd-MM-yyyy.

When converting the date time in Sql server is using the mm-dd-yyyy format. But I’m supplying the dd-mm-yyyy format date time.

I tried number of things none of them worked for me

1. Tried changing the default language and get the date time format

- exec sp_configure 'default language', 2057
reconfigure

- did not work

EXEC sp_defaultlanguage 'my user name', 'British'

- did not work

(Ref: http://www.cactushop.com/support/UKUS-date-format-issues-with-MS-SQLconversion-errors-or-blank-pages__592__.htm)

2. Tried a registry hack by opening regedit, and get the following 3 language keys and change it to decimal 2057:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup\CurrentVersion]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Setup
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup]

(Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891)

- did not work

3. Every thing in the Regional and Language options to UK and British with the date time format input language keyboard and every think else I could think of, which could link to US English or US date format Did not work

4. even went into the extend of modifying the date format on a Windows machine for new users account by editing the HKEY_USERS registry key and creating a new user - Did not work

(Ref: http://www.windowsitpro.com/Article/ArticleID/39407/39407.html )

5. Uninstall and reinstall SQL server express several time and did the steps 1 – 4 where applicable – did not work….

If anyone has any idea of what I have to do to change the date time format in the SQL Server 2005 to use the dd-mm-yyyy format for dates....

Please help me or point me in the direction in which I have to look for an answer.

Thank you very much….

Some SQL Server datetime is language dependent, there is a guide below you can use to change it.

http://www.karaszi.com/SQLServer/info_datetime.asp|||

Thanks Caddre for the post…

Your suggestion confirm me that the datetime format I’m using is Numeric one, which is LANGUAGE dependent… therefore my question of is it with sql server login language ? I guess valid…

……………………………………………..

The problem happening in my ASP.net application; I’m connecting to the Sql Server using connection-string : <add name="conn" connectionString="Data Source=hostname;Initial Catalog=dbname;User Id=myname;Password=password;" providerName="System.Data.SqlClient"/>

Here the default language of this ‘myname’ user is British English

In some installation of Sql Server when I supply datetime in “yyyy-mm-dd HH:mms.ms” format( E.g.: '2007-08-27 14:12:19.590') it work fine…

But in some other installation of Sql Server it throws the flowing exception

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.”

When I run the flowing command in both installations myname login, I’m getting the same result… as follows...

select SYSTEM_USER

- myname

select @.@.language

- British

I couldn’t find out what might be the problm between this two insallations?!!!!!

Any one have any suggestion? Please……………….

|||

You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application. I have covered that in the thread below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1837895&SiteID=1

|||

[You don't understand you need to change to language neutral format and you need to use overloads of the DateTime.ToString and other formatting for .NET DateTime in the application]

I’m aware that in order to avoid language dependence or language dependent format problems; I have to change to language neutral format, and my datetime format is not a language neutral format….

But the application is a legacy asp.net application and it was working fine in many client places…

In the app - No Stored procedures used for insert, update or select – any one to change it …All the database select, insert, and update are inline-sql-statements in the application which are .net assemblies (dlls)…. and I can not change the application - because I’m not the developer of the application….

The only option available for me is to find out a way to change the date time format of the login user so that it won’t throw exception after 12^th of each month…. That’s what I was trying to explain in my first post…

is there any solution for this?

I hope I’m clear on my description now…. sorry if I’m not clear on previous posts…

PS: - Please let me know if I’m not clear in this post ….or… if I should post this question to a different MSDN Forum…

Thank you…

|||

In the first place an application with inline SQL can get SQL injection and you have only two options either use the IsDate function or try using the British locale configuration in control panel. If that did not work you need to ALTER all the columns with Varchar as DateTime and make all the correction needed because client connection issue with bad code needs to be fixed.

http://msdn2.microsoft.com/en-us/library/aa176553(SQL.80).aspx

http://www.sql-server-helper.com/error-messages/msg-242.aspx

|||Hi all,
I am also having the SAME problem using MS ACCESS - supposedly an end user tool

I am a programmer of 30 years standing, so have some experience in building reliable inter application comms.

Now, I understand that the underlying technology is probably .NET

and that SQL is a bit vague on default date formats

HOWEVER

1) End users should not be exposed to this type of technologic problem
2) The MS Java driver gets it RIGHT FIRST TIME regardless of the regional and login settings in force
3) Similar problems have persisted for 15 or so years (Access, VB, Excel)

There is a work around - if your user will accept it - set Regional on the workstation to YYYY-MM-dd

MS - when can a more generic solution be delivered?
a) My customer is a MS solution provider
b) He does not want to migrate to YYY-MM-dd format since he DOES NOT KNOW WHAT THE IMPACT WILL BE ON HIS OTHER APPLICATIONS, and cannot afford the downtime in finding out
c) Should he change, there will be significant retraining of staff and losses due to incorrect data entry
d) All he sees is that he cant migrate from ACCESS / MDB to Access/SQL Server easily (both are MS product)
e) He has asked whether or not he should migrate to Java / Jasper / Mysql !

THE SOLUTION

MS - this should be in your court

1) You have 3 layers Access (or .net) , ODBC and SQL Svr
2) The first two are always on the client and thus can look at the same regional settings.
3) The .ODBC layer can interrogate the MS SQL server (or any other server for that matter) and establish what translations are required - or more simply establish its own convention e.g issue a SET DATE BRIT after establishing the connection. You could even invent a foolproof format of your own within proprietary extensions.
4) Workstation layer can look at regionalisation and ODBC setup options to determine connectivity
5) MS could even supply date format string options on the ODBC setup to define application and server preferred formats

RESULT

- ALL end user apps can now use SQL dates without mishap
- Bad applications that dont look at regionalisation can be catered for by configuring ODBC.
- User administrator can setup separate ODBC channesl and translation for all app variants

Everyone wins.|||

Thanks every one for the suggestions...

I have gone down the path of changing the windows Regional and Language options on the workstation to YYYY-MM-dd… (It worked….….)

And the problem of my head temporarily…