SQL 中比较日期的查询

我有张桌子，上面都是11月份的日期。 我写了这个查询

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

这个查询应该返回11月(11月)发生的所有事情，因为它发生在 之前的日期“2013-04-12”(12月)

但它只返回发生在比04(2013-04-12)少几天的可用日期

会不会只是比较一天的部分，而不是整个日期？

怎么补救？

Created _ date 的类型为 约会

日期格式默认为 Yyyy-dd-MM

804079 次浏览
小开
最佳答案

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 4th, you should write '20131204'. If you want to compare with April 12th, 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.

小开

Date format is yyyy-mm-dd. So the above query is looking for records older than 12Apr2013

Suggest you do a quick check by setting the date string to '2013-04-30', if no sql error, date format is confirmed to yyyy-mm-dd.

小开

Try like this

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

please try with below query

select id,numbers_from,created_date,amount_numbers,SMS_text
from Test_Table
where
convert(datetime, convert(varchar(10), created_date, 102))  <= convert(datetime,'2013-04-12')
小开

You put <= and it will catch the given date too. You can replace it with < only.

小开

If You are comparing only with the date vale, then converting it to date (not datetime) will work

select id,numbers_from,created_date,amount_numbers,SMS_text
from Test_Table
where
created_date <= convert(date,'2013-04-12',102)

This conversion is also applicable during using GetDate() function

小开

Try to use "#" before and after of the date and be sure of your system date format. maybe "YYYYMMDD O YYYY-MM-DD O MM-DD-YYYY O USING '/ O \' "

Ex:

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

Convert them to dates in the same format and then you can compare. Τry like this:

where convert(date, created_date,102) <= convert(date,                                  /*102 is ANSI format*/
substring('2013-04-12',1,4) + '.' + /*year*/
substring('2013-04-12',9,2) + '.' + /*month*/
substring('2013-04-12',6,2)         /*day*/
,102)
小开

you can also use to_char(column_name, 'YYYY-MM-DD) to change format

小开

Below query can be used to find the records of month November for the year 2013.

Select id,numbers_from,created_date,amount_numbers,SMS_text
from Test_Table
where Month(created_date) = 11 and Year(created_date) = 2013

提交答案