he conversion of a char data type to a datetime data type resulted in an out-of-range

adamhw · December 22nd, 2009, 05:26 AM

Hi there

I'm getting the following error:

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

This is the statement that's causing it:

SELECT p.productid, p.subcategoryid, p.productname, p.partnumber, p.rrp, p.cost, p.enddate, p.weight, p.tagline, LEFT(CAST(p.extrainformation as varchar(100)), 100) AS 'extrainformation', p.imagename, p.imagename_m, p.imagename2, p.imagename3, st.id, st.stock, p.onspecial, 1 As fIsSpecial, p.sequence As sequence FROM product p, tbl_stock st WHERE DATEDIFF(day, p.enddate, GETDATE()) < 0 AND p.deleted = 0 AND p.hide = 0 AND st.id = p.stock AND p.enddate = '31/12/2009' AND p.promotionid = '9' ORDER BY fIsSpecial DESC, sequence

I'm not sure how to go about fixing it?

many thanks

Adam

om_prakash · December 23rd, 2009, 03:27 AM

It is not picking the data format correctly.

p.enddate = '31/12/2009'

change above to

p.enddate = '12/31/2009'

adamhw · December 23rd, 2009, 04:29 AM

Hi there

thanks for your reply - but p.enddate is in the format 31/12/2009?

Should it be different?

thanks

Adam

om_prakash · December 23rd, 2009, 07:18 AM

To Use DMY format you can try the following:
SET DATEFORMAT Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.

Code:

SET DATEFORMAT dmy;
SELECT p.productid, p.subcategoryid, p.productname, p.partnumber, p.rrp, p.cost, p.enddate, p.weight, p.tagline, LEFT(CAST(p.extrainformation as varchar(100)), 100) AS 'extrainformation', p.imagename, p.imagename_m, p.imagename2, p.imagename3, st.id, st.stock, p.onspecial, 1 As fIsSpecial, p.sequence As sequence FROM product p, tbl_stock st WHERE DATEDIFF(day, p.enddate, GETDATE()) < 0 AND p.deleted = 0 AND p.hide = 0 AND st.id = p.stock AND p.enddate = '31/12/2009' AND p.promotionid = '9' ORDER BY fIsSpecial DESC, sequence
SET DATEFORMAT mdy;