December 7, 2004 at 10:41 pm
If you set your Regional Settings to 'English (United Kingdom)', then this query does not work:
SELECT TOP 1 CAST('27/10/2004 10:15:23' AS smalldatetime) FROM sysobjects
It generates the following error message:
"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
Why is that? 'English (United Kingdom)' uses dd/mm/yyyy as the short date format - so why does Sql Server not recognize '27/10/2004' as a date?
This query does not give an error:
SELECT TOP 1 CAST('10/8/2004 10:15:23' AS smalldatetime) FROM sysobjects
but it reports October 8th (instead of August 10th).
So, it appears that Sql Server is not respecting the regional settings. Is there anyway to resolve this issue?
December 8, 2004 at 12:47 am
use SET DATEFORMAT to explicitly define dateformat when handling dates. (ie. SET DATEFORMAT dmy).
In general, for international settings, dateformats should be handled with care.
HTH
Billy
December 8, 2004 at 4:09 am
Probably the "best" answer is to use one of the two generally accepted independent formats. You can read about it, for example, here:
http://www.karaszi.com/sqlserver/info_datetime.asp
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply