在SQL中查询比较日期 [英] Query comparing dates in SQL

问题描述

我有一张桌子，日期全部发生在11月份。
我写了这个查询

 从Test_Table $ b中选择id，numbers_from，created_date，amount_numbers，SMS_text 
 $ b其中
 created_date< ='2013-04-12'
  

此查询应该返回在11月（11月）发生的所有事情，因为它发生在之前的日期'2013-04-12'（12月）

但是只有返回发生在04天以内的可用日期（2013- 04 -12）

是否只是比较一天部分？而不是整个日期？

如何解决这个问题？

Created_date的类型为 date

日期格式是默认值 yyyy-dd-MM

解决方案

而不是2013-04-12，其含义取决于当地文化，使用被认为是文化不变格式的20130412。

如果要与12月4日 ^th 进行比较，您应该写20131204。如果你想比较4月12日 ^th ，你应该写'20130412'。

文章 Write International Transact-SQL语句解释了如何编写文化不变的语句：

使用其他API或Transact-SQL脚本，存储过程和触发器的应用程序应使用未分隔的数字字符串。例如，yyyymmdd为19980924。

编辑

由于您使用的是ADO，最好的选择是参数化查询，并将日期值作为日期参数传递。这样，您可以完全避免格式问题，并获得参数化查询的性能优势。

更新

要在文字中使用ISO 8601格式，必须指定所有元素。要从datetime文档的ISO 8601部分中引用

要使用ISO 8601格式，必须以格式指定每个元素。这也包括T，冒号（:)和期间（。），以格式显示。

...第二个组件的分数是可选的。时间组件以24小时格式指定。

I have a table with dates that all happened in the month November. I wrote this query

select id,numbers_from,created_date,amount_numbers,SMS_text 
from Test_Table
where 
created_date <= '2013-04-12'

This query should return everything that happened in month 11 (November) because it happened before the date '2013-04-12' (in December)

But it's only returning available dates that happened in days lesser than 04 (2013-04-12)

Could it be that it's only comparing the day part? and not the whole date?

How to fix this?

Created_date is of type date

Date format is by default yyyy-dd-MM

解决方案

Instead of '2013-04-12' whose meaning depends on the local culture, use '20130412' which is recognized as the culture invariant format.

If you want to compare with December 4^th, you should write '20131204'. If you want to compare with April 12^th, you should write '20130412'.

The article Write International Transact-SQL Statements from SQL Server's documentation explains how to write statements that are culture invariant:

Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the unseparated numeric strings. For example, yyyymmdd as 19980924.

EDIT

Since you are using ADO, the best option is to parameterize the query and pass the date value as a date parameter. This way you avoid the format issue entirely and gain the performance benefits of parameterized queries as well.

UPDATE

To use the the the ISO 8601 format in a literal, all elements must be specified. To quote from the ISO 8601 section of datetime's documentation

To use the ISO 8601 format, you must specify each element in the format. This also includes the T, the colons (:), and the period (.) that are shown in the format.

... the fraction of second component is optional. The time component is specified in the 24-hour format.

这篇关于在SQL中查询比较日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！