TSQL 使用 dd-mm-yyyy 和 yyyy-mm-dd 有什么区别 [英] TSQL what is the difference between using dd-mm-yyyy and yyyy-mm-dd

问题描述

出于兴趣，我有一个相当基本的问题.在 T-SQL(SSMS 等)中，在幕后"中使用 dd-mm-yyyy 和 yyyy-mm-dd 有什么区别?where 语句.

I have a rather basic question out of interest. In T-SQL (SSMS or the like), what is the difference, "behind the scenes", between using dd-mm-yyyy and yyyy-mm-dd in a where statement.

例如，这两个查询给了我不同的结果

For example, these two queries give me different results

select * from DB..table where application_date > '01-01-2019' and application_date < '01-06-2019' order by app_ID;

select * from DB..table where application_date > '2019-01-01' and application_date < '2019-06-01' order by app_ID;

似乎第一个是第二个的子集.相比之下，第一个给了我 83 条记录，而第二个给了我超过 11000 条带零钱的记录.

It seems that the first is a sub set of the second. For comparison, the first gives me 83 records, while the second gives me over 11 thousand with change.

如果有人能启发我，理解起来会很有趣.

It would be interesting to understand, if someone could enlighten me.

推荐答案

文字 '01-06-2019' 实际上被解释为 2019-01-06，这与第二个查询使用的 2019-06-01 不是相同的东西.

The literal '01-06-2019' is actually interpreted to be 2019-01-06, which is not the same thing as 2019-06-01, which the second query is using.

您可以阅读 SQL Server 的 文档 涵盖了支持的日期文字格式.但总的来说，尽量坚持使用 ISO 日期格式，几乎每个 SQL 数据库都支持这种格式.因此，您应该始终尝试使用查询的第二个版本.

You may read SQL Server's documentation covering supported date literal formats. But in general, try to stick with ISO date formats, which are supported by pretty much every SQL database. So, the second version of your query is the one you should always try to use.

这篇关于TSQL 使用 dd-mm-yyyy 和 yyyy-mm-dd 有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！