使用'between'与varchar（sql server） [英] use 'between' with varchar (sql server)

问题描述

使用SQL Server 2005 Express。

Using SQL Server 2005 Express.

(
    CONVERT(VARCHAR(8), R.reviewStart, 108) between CONVERT(VARCHAR(8), M.meetingStart, 108) and CONVERT(VARCHAR(8), M.meetingEnd, 108) OR
    CONVERT(VARCHAR(8), R.reviewEnd, 108) between CONVERT(VARCHAR(8), M.meetingStart, 108) and CONVERT(VARCHAR(8), M.meetingEnd, 108) OR
    CONVERT(VARCHAR(8), M.meetingStart, 108) between CONVERT(VARCHAR(8), R.reviewStart, 108) and CONVERT(VARCHAR(8), R.reviewEnd, 108) OR
    CONVERT(VARCHAR(8), M.meetingEnd, 108) between CONVERT(VARCHAR(8), R.reviewStart, 108) and CONVERT(VARCHAR(8), R.reviewEnd, 108)
)

数据时间转换为varchar后，between是否仍然有预期的行为？

Will the "between" still have the expected behavior after the datetimes have been converted to varchar?

谢谢

推荐答案

是的，取决于你的预期行为的意思。 BETWEEN运算符将这些操作数视为varchars，并相应地应用其比较规则：

Yes, depending on what you mean by expected behavior. The BETWEEN operator will treat these operands as varchars, and apply its comparison rules accordingly:

如果值为
，BETWEEN返回TRUE test_expression大于或
等于begin_expression
的值，小于或等于
end_expression的值。

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

现在，我可以看到很多潜在的问题，比较字符串和期待日期比较行为。我的测试中没有看到任何内容，但请仔细查看您的数据。 CONVERT是否返回24小时的时间，具有相应的前导零？

Now, I can see a lot of potential problems, comparing strings and expecting date comparison behavior. I haven't seen any in my tests, but look carefully at your data. Is the CONVERT returning 24-hour time, with the appropriate leading zeroes?

这个问题有一些其他的方法来比较无数据时间，而不是将它们转换为varchars。

This question has some other approaches to comparing dateless-times, other than converting them to varchars.

另外，注意null日期，这将导致相应的WHERE条件返回false（实际上是未知的）。

Also, watch for null dates, which will cause the corresponding WHERE condition to return false (actually, unknown).

在你的另一个问题中，你表示你是得到一个错误。如果是这样，你可以发布吗？

In your other question, you indicated that you were getting an error. If so, can you post that?

这篇关于使用'between'与varchar（sql server）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！