select * from 表 where datediff( day,字段名,'2000-1-1' ) <=0这种格式的字符串会隐会转换成datetime
以上的语句是能通过的, 但是放到我查询里面,就出现转换表达式到日期型时算法溢出错误:我的语句是:曹浩兵(曹浩兵) 12:49:46 SELECT TOP 10 c.* FROM dbo.tbl_dealer_cuxiao c INNER JOIN dbo.tbl_Dealer d ON c.cdealer_code = d.cDealer_Code INNER JOIN dbo.tbl_City t ON d.cCity_Code = t.cCity_Code WHERE (t.cCity_Code = '0010') AND (d.cDealer_Code IN (SELECT DISTINCT cdealer_code FROM tbl_price WHERE ctype_code = '0305')) AND (CAST(c.cstartdate AS datetime) >= '2004-06-01') AND (CAST(c.cenddate AS datetime) <= '2004-09-15') ORDER BY c.cuxiaoid DESC
.................... WHERE ctype_code = '0305')) AND (CAST(c.cstartdate AS datetime) >= cast('2004-06-01' as datetime)) AND (CAST(c.cenddate AS datetime) <= cast('2004-09-15' as datetime)) ORDER BY c.cuxiaoid DESC
也还是出现了:Arithmetic overflow error converting expression to data type datetime.的错误
SELECT TOP 10 c.*
FROM dbo.tbl_dealer_cuxiao c INNER JOIN
dbo.tbl_Dealer d ON c.cdealer_code = d.cDealer_Code INNER JOIN
dbo.tbl_City t ON d.cCity_Code = t.cCity_Code
WHERE (t.cCity_Code = '0010') AND (d.cDealer_Code IN
(SELECT DISTINCT cdealer_code
FROM tbl_price
WHERE ctype_code = '0305')) AND (CAST(c.cstartdate AS datetime)
>= '2004-06-01') AND (CAST(c.cenddate AS datetime) <= '2004-09-15')
ORDER BY c.cuxiaoid DESC
WHERE ctype_code = '0305')) AND (CAST(c.cstartdate AS datetime)
>= cast('2004-06-01' as datetime)) AND
(CAST(c.cenddate AS datetime) <= cast('2004-09-15' as datetime))
ORDER BY c.cuxiaoid DESC
AND
(CAST(c.cenddate AS datetime) <= cast('2004-09-15' as datetime))
后面这半句去掉了,就出来了,后来检查cenddate 字段,里面没有不符合要求的