British English Date Problem - ISO-dates not accepted (64-bit)

Problem exists on a 64-bit SQL Server (although the same result occurs on a
32-bit platform), where we have installed MS-SQL with the British English
locale.
We are experiencing problems when passing ISO format dates (yyyy-mm-dd)
within queries. UK date format (25/12/2004) are ok but as soon as an iso
date is used '2004-12-25' an error occurs.
<For example:>
Select * from tbltest where testdate > '2003-04-16 00:00:00'
<Returns error:>
Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
There must be something that we are missing here, as changing the users
locale to the default 'English' (US English) corrects the problem.
Thanks in advance
IanHave a look at these:
http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm Datetime Searching
I would use the format of 'yyyymmdd' instead.
--
Andrew J. Kelly SQL MVP
"Ian" <Ian@.discussions.microsoft.com> wrote in message
news:77EB65F8-40FE-4BE6-9058-425CFA1F36B8@.microsoft.com...
> Problem exists on a 64-bit SQL Server (although the same result occurs on
> a
> 32-bit platform), where we have installed MS-SQL with the British English
> locale.
> We are experiencing problems when passing ISO format dates (yyyy-mm-dd)
> within queries. UK date format (25/12/2004) are ok but as soon as an iso
> date is used '2004-12-25' an error occurs.
> <For example:>
> Select * from tbltest where testdate > '2003-04-16 00:00:00'
> <Returns error:>
> Server: Msg 296, Level 16, State 3, Line 1
> The conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value.
> There must be something that we are missing here, as changing the users
> locale to the default 'English' (US English) corrects the problem.
> Thanks in advance
> Ian|||thanks for the info...
Ian
"Andrew J. Kelly" wrote:
> Have a look at these:
>
> http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
> http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
> Datetimes
> http://www.murach.com/books/sqls/article.htm Datetime Searching
>
> I would use the format of 'yyyymmdd' instead.
> --
> Andrew J. Kelly SQL MVP
>
> "Ian" <Ian@.discussions.microsoft.com> wrote in message
> news:77EB65F8-40FE-4BE6-9058-425CFA1F36B8@.microsoft.com...
> > Problem exists on a 64-bit SQL Server (although the same result occurs on
> > a
> > 32-bit platform), where we have installed MS-SQL with the British English
> > locale.
> >
> > We are experiencing problems when passing ISO format dates (yyyy-mm-dd)
> > within queries. UK date format (25/12/2004) are ok but as soon as an iso
> > date is used '2004-12-25' an error occurs.
> >
> > <For example:>
> >
> > Select * from tbltest where testdate > '2003-04-16 00:00:00'
> >
> > <Returns error:>
> >
> > Server: Msg 296, Level 16, State 3, Line 1
> > The conversion of char data type to smalldatetime data type resulted in an
> > out-of-range smalldatetime value.
> >
> > There must be something that we are missing here, as changing the users
> > locale to the default 'English' (US English) corrects the problem.
> >
> > Thanks in advance
> > Ian
>
>