Arithmetic overflow error converting expression to datatype datetime

[dgiet]

Hi all,

I have a web application based on CFMX 6.1, IIS 5 and MS-SQL 8.0
On my development server the application works fine.
On my production server i have this error
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Arithmetic overflow error
converting expression to data type datetime.

with this request
SELECT DISTINCT eft_element.elt_id ,a1.pho_date_1
FROM eft_element,PHOTO a1,photo a2
WHERE eft_element.obj_id = 'objet000000000000012'
AND cast(convert(nchar(10), a2.pho_date_1, 103) as
datetime)>=cast(convert(nchar(8), '5-5-2004', 103) as datetime)
AND a1.elt_id = eft_element.elt_id
AND a2.elt_id = eft_element.elt_id
AND eft_element.elt_id NOT IN (
select eft_element.elt_id from eft_element where
eft_element.elt_id='element0000000000001'
) order by a1.pho_date_1

The only difference between the 2 servers is the default language for the
database : french for development and english for production but date format
are the same for French and English

The datatype of the column ph_date_1 is datetime

When i run this request into MS-SQL request analyzer there is no error.

Have you an idea?

[dgiet]

Well i have found an answer on this site [url]http://www.prezzatech.com/kb/articles/kb-1029-survey_software_arithmetic_overflow_datetime.asp[/url]

[The ScareCrow]

I also think you may have problems with this as well
AND cast(convert(nchar(10), a2.pho_date_1, 103) as
datetime)>=cast(convert(nchar(8), '5-5-2004', 103) as datetime)

your converting dates to character string then comparing them, but on the left
hand side of the comparision you have 10 characters and on the right you have 8.

But I'm not sure why you would want to convert them at all,

AND a2.pho_date_1 >= #CreateODBCDate("5-5-2004")#

Ken