SQL Server:日期时间超出范围错误 [英] SQL Server: datetime out of range error
问题描述
我有这个查询
SELECT W.NUMBER,B.NAME,Br.NAME,W.WDATE,W.REPAIRSTATUS,W.REPAIRCOST,W.REMARK
FROM Work_Order AS W,Brands AS B,Branches AS Br
WHERE W.BRANDID = B.ID AND W.BRANCHID = Br.ID
AND CAST(WDATE as DATETIME) < CAST('09/18/2012' AS DATETIME)
系统响应
char 数据类型到 datetime 数据类型的转换导致超出范围的日期时间值.
也使用这个查询它给出了同样的错误
also using this query it gives the same error
SELECT W.NUMBER,B.NAME,Br.NAME,W.WDATE,W.REPAIRSTATUS,W.REPAIRCOST,W.REMARK
FROM Work_Order AS W,Brands AS B,Branches AS Br
WHERE W.BRANDID = B.ID AND W.BRANCHID = Br.ID
ORDER BY CAST(W.WDATE AS DATETIME)
WDATE 就像'09/03/2012',它是来自 jquery ui datepicker 的输入,具有 mm/dd/yyyy 日期格式
WDATE is like '09/03/2012' and it is an input from jquery ui datepicker with mm/dd/yyyy date format
请帮帮我,我不知道如何解决.
Please help me I don't know how to fix it.
我正在研究 SQL Server 2005.
I am working on SQL Server 2005.
我用另一个 sql server 2005 尝试了相同的查询,它工作正常,所以请教如何使用 sql server 设置修复这个错误??
I tried the same queries with another sql server 2005 and it's working fine so please how to fix this error with a sql server settings ??
推荐答案
我假设你的 WDATE 是一个 varchar/char 而不是 datatime ,你可以这样转换,但是我建议你将数据类型更改为约会时间.试试这个:
I assume your WDATE is a varchar / char and not datatime as it should be, you can cast it like this, however i recommand that you change the datatype to datetime. Try this:
SELECT W.NUMBER,B.NAME,Br.NAME,W.WDATE,W.REPAIRSTATUS,W.REPAIRCOST,W.REMARK
FROM Work_Order AS W,Brands AS B,Branches AS Br
WHERE W.BRANDID = B.ID AND W.BRANCHID = Br.ID
AND CONVERT( DATETIME, WDATE, 101) < CONVERT( DATETIME, '09/18/2012', 101)
试试这个代码,它应该能找到大部分无效的日期
Try this code, it should find most of the invalid dates
SELECT WDATE, W.NUMBER,B.NAME,Br.NAME,W.WDATE,W.REPAIRSTATUS,W.REPAIRCOST,W.REMARK
FROM Work_Order AS W,Brands AS B,Branches AS Br
WHERE W.BRANDID = B.ID AND W.BRANCHID = Br.ID
AND WDATE not like '[0-1][0-9]/[0-3][0-9]/20[0-1][0-9]'
这篇关于SQL Server:日期时间超出范围错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!