June 10, 2008 at 9:10 am
Earlier today, I created a new login for use with one of our databases. That database contains a Table function that returns a report of all data between 01-Jan of one year and 31-Dec of another year. There has never previously been any problems with this function, but when the new login called this function, it produced an error:
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
Eventually, I ended up testing with this script:
--- A) try to convert string from YYYY-MM-DD format into datetime
select CAST ('2008-12-31' as datetime)
--- B) try to convert string from YYYY-DD-MM format into datetime
select CAST ('2008-31-12' as datetime)
For the old logins, statement A succeeded, but Statement B failed. For the new login, the reverse was true!
All the logins were created on the same machine, and run against the same database. But for some reason, the new login converts the date string in a different manner to the old logins.
Can anyone suggest why I am getting this strange behaviour?
June 10, 2008 at 9:17 am
Hello Mark,
Check the user's default language
I am getting these results when I switch the language for my login from English to German and back
Do not forget to disconent and re-connect this login after making changes to the login
Regards,Yelena Varsha
June 10, 2008 at 9:26 am
indeed !
if you need to have several culture/language/internationalisation (I18n!) then sobeit
- but the database engine needs to know how to interpret input from client-side
you can override an individual session (initialised based on language as already said) by
SET DATEFORMAT { format | @format_var }
e.g.
SET DATEFORMAT ydm -- Set date format to year, day, month
SET DATEFORMAT mdy -- Set date format to month, day, year
HTH
Dick
June 10, 2008 at 10:09 am
Here is how I solved the problem:
(a) I changed my new login to have Default Language = "English", and this solved the problem.
(b) I've checked all our existing logins, and they all have Default Language = "English".
Now here is the explanation: when I created my new login, I copied all the settings from my Windows login. This turned out to be a bad idea.
My Windows Login had Default Language = "British English". But investigation revealed that it behaves like an "English" SQL Server login, regardless of whether its Default Language is "British English" or "English".
For SQL Server Logins, the story is different:
SQL Server Logins with the Default Language "British English" can convert a date from "YYYY-DD-MM" format, but not "YYYY-MM-DD".
SQL Server Logins with the Default Language "English" can convert a date from "YYYY-MM-DD" format, but not "YYYY-DD-YY".
From my knowledge of American and British date formats, and interpreting "English" as US English, this is the exact opposite of what I expected!
June 10, 2008 at 10:10 am
Dick,
I have now solved the problem. I replied to my original post, as more than one person helped me.
Many thanks for your prompt help,
Mark
June 10, 2008 at 10:11 am
Yelena,
I have now solved the problem. I replied to my original post, as more than one person helped me.
Many thanks for your prompt help,
Mark
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply