SQL 如何使用带有日期条件的选择？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/696438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I use select with date condition?
提问by TheTXI
In sqlserver, how do I compare dates? For example:
在 sqlserver 中，如何比较日期？例如：
Select * from Users where RegistrationDate >= '1/20/2009'
从注册日期 >= '1/20/2009' 的用户中选择 *
(RegistrationDate is datetime type)
（RegistrationDate 是日期时间类型）
Thanks
谢谢
回答by TheTXI
If you put in
如果你投入
SELECT * FROM Users WHERE RegistrationDate >= '1/20/2009'
it will automatically convert the string '1/20/2009'into the DateTimeformat for a date of 1/20/2009 00:00:00. So by using >=you should get every user whose registration date is 1/20/2009 or more recent.
它会自动将字符串'1/20/2009'转换DateTime为日期的格式1/20/2009 00:00:00。因此，通过使用>=您应该获得注册日期为 2009 年 1 月 20 日或更晚的每个用户。
Edit: I put this in the comment section but I should probably link it here as well. This is an article detailing some more in depth ways of working with DateTime's in you queries: http://www.databasejournal.com/features/mssql/article.php/2209321/Working-with-SQL-Server-DateTime-Variables-Part-Three---Searching-for-Particular-Date-Values-and-Ranges.htm
编辑：我把它放在评论部分，但我也应该把它链接到这里。这是一篇文章，详细介绍了在查询中使用 DateTime 的一些更深入的方法：http: //www.databasejournal.com/features/mssql/article.php/2209321/Working-with-SQL-Server-DateTime-Variables-第三部分---搜索特定日期值和范围.htm
回答by Shiju Daniel
select sysdate from dual
30-MAR-17
select count(1) from masterdata where to_date(inactive_from_date,'DD-MON-YY'
between '01-JAN-16' to '31-DEC-16'
12998 rows
回答by Patrick McDonald
回答by KM.
I always get the filter date into a datetime, with no time (time= 00:00:00.000)
我总是将过滤器日期转换为日期时间，没有时间（时间 = 00:00:00.000）
DECLARE @FilterDate datetime --final destination, will not have any time on it
DECLARE @GivenDateD datetime --if you're given a datetime
DECLARE @GivenDateS char(23) --if you're given a string, it can be any valid date format, not just the yyyy/mm/dd hh:mm:ss.mmm that I'm using
SET @GivenDateD='2009/03/30 13:42:50.123'
SET @GivenDateS='2009/03/30 13:42:50.123'
--remove the time and assign it to the datetime
@FilterDate=dateadd(dd, datediff(dd, 0, @FilterDateD), 0)
--OR
@FilterDate=dateadd(dd, datediff(dd, 0, @FilterDateS), 0)
You can use this WHERE clause to then filter:
您可以使用此 WHERE 子句进行过滤：
WHERE ColumnDateTime>=@FilterDate AND ColumnDateTime<@FilterDate+1
this will give all matches that are on or after the beginning of the day on 2009/03/30 up to and including the complete day on 2009/03/30
这将给出在 2009/03/30 当天开始或之后的所有比赛，直到并包括 2009/03/30 的完整日
you can do the same for START and END filter parameters as well. Always make the start date a datetime and use zero time on the day you want, and make the condition ">=". Always make the end date the zero time on the day after you want and use "<". Doing that, you will always include any dates properly, regardless of the time portion of the date.
您也可以对 START 和 END 过滤器参数执行相同操作。始终将开始日期设为日期时间，并在所需日期使用零时间，并使条件为 ">="。始终将结束日期设为您想要的后一天的零时间并使用“<”。这样做，无论日期的时间部分如何，您都将始终正确地包含任何日期。
回答by jle
Another feature is between:
另一个特点是间：
Select * from table where date between '2009/01/30' and '2009/03/30'
回答by LONG
if you do not want to be bothered by the date format, you could compare the column with the general date format, for example
如果您不想被日期格式打扰，则可以将该列与一般日期格式进行比较，例如
select *
From table
where cast (RegistrationDate as date) between '20161201' and '20161220'
select *
From table
where cast (RegistrationDate as date) between '20161201' and '20161220'
make sure the date is in DATE format, otherwise cast (col as DATE)
确保日期为 DATE 格式，否则 cast (col as DATE)
