SQLServer 2005: Error converting date 'yyyy-mm-dd' in query

I have this condition in a query:

WHERE fielddate> = '2010-10-01 'AND fielddate <= '2010-10-18'

to migrate the database to another server (2008) with the same engine da开发者_运维技巧tabase (sqlserver-2005 Express), returned error:

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

But if I make this query works

WHERE fielddate> = '20101001 'AND fielddate <= '20101018'

the collation type is the same:

Modern_Spanish_CI_AS

and all the other features that I saw are the same.

I do not know what the problem is

Thanks.

Check the locale of the server, but you can also use CONVERT and specify the date format :)

http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

The way these ambiguous date formats are interpreted depends upon the default language for the login. Presumably your login in one instance has a different language than in the other instance.

It is best practice to avoid these ambiguous formats.

The problem stems from the fact that your data is stored in a character field rather than a datetime field. Especially since different collations will write / read differently into character fields.

Make the Change

My first suggestion would be to change the data type of the field to DateTime. Since you are going to a brand new SQL 2008 database, you may want to take advantage and change any dates stored in character formats to actual DateTime

CAST and CONVERT

You can use CAST to explicitly convert the field as in

WHERE 1=1
AND CAST (fielddate AS DATETIME) >= '2010-10-01 'AND 
CAST (fielddate AS DATETIME) <= '2010-10-18'