datediff 的 怪问题！

Question

一个表中有类型为：varchar的列RQ1，我运行以下SQL 转换为日期型：SELECT convert(datetime,RQ1),* FROM TABLE1（可以正常执行）但是我用以下SQL（用datediff进行判断时出错！）：
SELECT * FROM TABLE1 where  datediff(DAY,convert(datetime,getdate()),convert(datetime,RQ1))<=30错误提示为：
服务器: 消息 296，级别 16，状态 3，行 1从 char 数据类型到 smalldatetime 数据类型的转换导致 smalldatetime 值越界。
为什么？如果 有值不能转换为datetime 的话 ，应该第一句SQL 也会报错啊！

Answer 1

SELECT * FROM TABLE1 where  datediff(DAY,getdate(),RQ1) <=30 

Answer 2

SELECT * FROM TABLE1 where  datediff(DAY,getdate(),convert(datetime,RQ1)) <=30 SELECT * FROM TABLE1 where  datediff(DAY,getdate(),cast(RQ1 as datetime)) <=30 

Answer 3

不好意思，我写错了：应该是：SELECT * FROM TABLE1 where  datediff(DAY,getdate(),convert(datetime,RQ1)) <=30 但是 错误是一样的：服务器: 消息 296，级别 16，状态 3，行 1从 char 数据类型到 smalldatetime 数据类型的转换导致 smalldatetime 值越界。 

Answer 4

估计你的RQ1里有无效日期, 譬如2月30日什么的. 用CONVERT的时候, 一般不检查, 但是用DATEDIFF的时候一定会检查的. 建议LZ用isdate查一查你的RQ1是否都有效?

Answer 5

试试在CONVERT里设顶style(跟你的varchar格式一致的style)看看.

Answer 6

Smalldatetime conversion - be awareI have had a major T-SQL problem in the last 2 days that has had me tearing my hair out. I have now got a solution and I thought that it was worth sharing what I had learned - maybe I can prevent someone else from having the same problems.I was running a stored procedure that kept failing on me and I managed to trace the problem back to the following bit of code in an UPDATE statement:
CONVERT(smalldatetime,
CAST(vg.CalYearID AS VARCHAR(4)) + '-' +
RIGHT('0' + CAST(r.BGCancellationMonthID AS VARCHAR(2)),2) + '-' +
RIGHT('0' + cast(r.BGCancellationDayID AS VARCHAR(2)),2)
)
The error I was getting was:
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.The strange thing was that I was able to run the UPDATE statement on its own successfully from Query Analyser. What??? Very strange...and incredibly annoying. I was even able to copy the same bit of code from 1 QA window to another and it would fail in one window, succeed in another. Same query, same server, same data, same client, different result!!! Ridiculous.Anyway, I managed to get some help from SQLServerCentral.com forums and was eventually able to track down the problem. It turned out that SQL Server was having trouble deciding whether my date "2004-08-21" was in the form YYYY-MM-DD or YYYY-DD-MM. When I ran it from the stored proc SQL Server interpreted it as the 8th day of the 21st month (so it failed) whereas when running just the UPDATE query on its own it was interpreted as 21st day of the 8th month (so it worked).The way around this is obviously to be as unambiguous as possible when doing your conversions. In this case my fix was to use a style parameter of 120 in the CONVERT function. My code snippet now looks like this:
CONVERT(smalldatetime,
CAST(vg.CalYearID AS VARCHAR(4)) + '-' +
RIGHT('0' + CAST(r.BGCancellationMonthID AS VARCHAR(2)),2) + '-' +
RIGHT('0' + cast(r.BGCancellationDayID AS VARCHAR(2)),2) +
' 00:00:00', 120
) After a bit more testing I was able to come up with the following rules of thumb:1. If the data is specified as YYYYMMDD a cast or a convert will produce the right result2. If the data is specified as YYYY-MM-DD use a convert with the style parameter specified Hopefully this will stop people from having the same problems that I have had. It help me up for hours and hours so spending your time understanding the issues here could really help you in the long term. Click the link above to see a more detailed discussion on the discussion forum.-Jamie

Answer 7

我用isdate 检查了：select isdate(RQ1) FROM TABLE1结果显示列都为 1 。因为表中记录有几W条，所以不方便帖出数据！高手 救命啊！

Answer 8

试试在CONVERT里设顶style(跟你的varchar格式一致的style)看看.
因为SQL SERVER有时侯不能确定格式, 特别是在存储过程里.

Answer 9

试试在CONVERT里设顶style(跟你的varchar格式一致的style)看看. 
因为SQL SERVER有时侯不能确定格式, 特别是在存储过程里.能不能说的具体点啊！？？ 大哥

Answer 10

根据上面我贴的文章, 你的问题可能跟他的问题是一模一样的. 你看看你的varchar日期的格式是什么样的, 是YYYY-MM-DD还是YYYY-DD-MM还是其他的格式(如果是混合的, 那可能没救了.)

Answer 11

SELECT (convert(datetime,ydjyrq)) FROM table1我看了都是 “YYYY-MM-DD” 的啊！