Working in a culture which uses dd/mm/yyyy date formats can lead to some peculiar problems when using US-centric software. Past experience with this problem lead me to consistently express date values as yyyy-mm-dd for SQLServer. I was under the impression that this was a foolproof, culture-neutral technique and until now I have not had any problem with it.
Today I encountered a peculiar problem with casting or converting date-strings when different language settings are applied.
This is OK.
Whereas these two fail with the message: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Even stranger is that this succeeds
But this fails
Can anyone explain what is going on here?
If you took out the Hyphens in the date format it would work. See this URL for a list of Date formats and multi language compatability http://msdn.microsoft.com/en-us/library/ms180878.aspx.
Also see here for multi language TSQL statements. http://msdn.microsoft.com/en-us/library/ms191307.aspx. It basically says that you need to specify the style parameters with a convert statement
answered Jul 12, 2010 at 08:38 PM