conversion of char data type to a datetime

[peterempson]

My report uses a sql stored procedure. Works fine in sql 2000 but when adding it to the report receiving the error:

ODBC error:[Mocrosoft][ODBC SQL Server Driver] The conversion of char data type to a datetime data type resulted in an-out of ranged datetime value.

the line in the stored procedure causing the problem is as follows;

INSERT INTO temp_whereabouts VALUES (@pa_number, @street_number, @street_entry,cast(cast(year(@street_entry) as varchar)+'-'+cast(month(@street_entry) as varchar)+'-'+cast(day(@street_entry) as varchar)+' 23:59' as datetime))

any ideas how to fix it?

 

[SQLBill]

Add this to the beginning of the script and try it.

SET DATEFORMAT YMD

That tells SQLServer how to interpret the incoming date format.

You might also want to check that you are actually inputting it as YMD and not YDM.

-SQLBill

 

[peterempson]

Cheers SQLBill,

thats working now in Crystal, your a star!

 

[donutman]

But I don't see his star?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]