views:

34

answers:

3

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 database (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.

+1  A: 

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

ykatchou
It is not the locale of the *server* directly. It is the locale of the *login*.
Martin Smith
A: 

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.

Martin Smith
A: 

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'
Raj More
It is a string literal not a date stored in a char column. It will be implicitly cast to datetime anyway when used in a predicate against a datetime column which is why he sees the error message he is seeing.
Martin Smith
the system is great and I can not change it all
andres descalzo
Convert is better because you specify the format
ykatchou