SQL Server如何将日期与字符串文字进行比较？

I am modifying a query, here is my script:

我正在修改一个查询，这是我的脚本：

select 
CASE When EndDate='1/1/1900' 
THEN NULL 
ELSE EndDate END,*
from Members

This script simply compares date, if it is '1/1/1900' then returns null otherwise it returns the date.

此脚本只是比较日期，如果是'1/1/1900'则返回null，否则返回日期。

I can see in my database date is stored in the following format:

我可以在我的数据库中看到日期存储的格式如下：

1900-01-01 00:00:00.000

Question is how SQL Server is matching date like this when my pattern is different from the stored one. Also in date format I am not passing time element.

问题是当我的模式与存储的模式不同时，SQL Server如何匹配日期。同样在日期格式中，我没有传递时间元素。

1 个解决方案

#1

SQL Server converts the string literal you are passing ('1/1/1900') to a datetime value due to data type precedence (since datetime has higher precedence than string types). If you pass an invalid date as your string, e.g. '2/31/1900', you will get a conversion error (Msg 242) because SQL Server doesn't know what February 31st means. It is not trying to match a string that looks like what you are passing, it converts both to its internal representation for dates (more on that in my comment).

由于数据类型优先，SQL Server将您传递的字符串文字（'1/1/1900'）转换为日期时间值（因为datetime的优先级高于字符串类型）。如果您将无效日期作为字符串传递，例如'2/31/1900'，您将收到转换错误（Msg 242），因为SQL Server不知道2月31日的含义。它不是试图匹配一个看起来像你传递的字符串，它将两者都转换为日期的内部表示（更多关于我的评论）。

When dealing with dates specifically, stop thinking about a format except that when you pass string literals, m/d/y (or is that d/m/y?) is a terrible format to use. Much safer to use:

特别是在处理日期时，不要考虑格式，除了当你传递字符串文字时，m / d / y（或者是d / m / y？）是一种可怕的格式。更安全使用：

YYYYMMDD

Your query should read:

您的查询应为：

SELECT CASE When EndDate = '19000101' 
 THEN NULL ELSE EndDate END, ...other columns...
FROM dbo.Members;

This way, when you pass a date like September 8th, it is not misinterpreted by SQL Server, other readers, etc. Is 09/08/2013 September 8th or August 9th? Depends on what part of the world you're in, right? In your case it's okay because the day and month are the same, but this won't always be the case. Please see the following article:

这样，当您通过9月8日这样的日期时，它不会被SQL Server，其他读者等误解。是09/08/2013 9月8日还是8月9日？取决于你所在的世界的哪个部分，对吗？在你的情况下它是可以的，因为日期和月份是相同的，但情况并非总是如此。请参阅以下文章：

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

(Please, please, please read that link in its entirety.)

（请，请完整阅读该链接。）

Finally, if you are using DATETIME/SMALLDATETIME and are looking for values from a specific day, you should not be using equality at all, but rather a range query. For example, to find all the rows where EndDate falls on April 15th, 2013, regardless of time, you would say:

最后，如果您正在使用DATETIME / SMALLDATETIME并且正在查找特定日期的值，则不应该使用相等，而是使用范围查询。例如，要查找2013年4月15日EndDate所有行，无论何时，您都会说：

WHERE EndDate >= '20130415'
  AND EndDate <  '20130416'

(Read this link to understand why you don't want to use BETWEEN here.)

（阅读此链接以了解您不希望在此处使用BETWEEN的原因。）

If you are on SQL Server 2008 or better, you can still achieve sargability on this column with CONVERT, but this is a rare exception - usually you don't want to use a function against a column.

如果您使用的是SQL Server 2008或更高版本，您仍然可以使用CONVERT在此列上实现sargability，但这是一个罕见的例外 - 通常您不希望对列使用函数。

WHERE CONVERT(DATE, EndDate) = '20130415'

A couple of other comments - not directly related to your question, but peripheral observations about your code:

其他一些评论 - 与您的问题没有直接关系，但有关您的代码的外围观察：

Always use the schema prefix
始终使用架构前缀
Never use SELECT * in production
切勿在生产中使用SELECT *

#1