SQL 显示 | 从今天日期选择数据 | 仅限当天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26414501/
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
SQL display | select data from today date | current day only
提问by Ronaldinho Learn Coding
I have this query, it supposes to display data from current date (today) only
我有这个查询，它假设只显示当前日期（今天）的数据
SELECT * FROM Customer WHERE visitTime <= GETDATE() AND visitTime > GETDATE() - 1
where visitTimeis datetimetype
其中visitTime是日期时间类型
But it doesn't work well because I think problem is at AND visitTime > GETDATE() - 1but I don't know how to fix this, anybody has any suggestions?
但它不能很好地工作，因为我认为问题出在AND visitTime > GETDATE() - 1但我不知道如何解决这个问题，有人有什么建议吗？
回答by jpw
Look at this example:
看这个例子：
declare @visitTime datetime ='2014-10-16 23:59:59.000'
select GETDATE() GETDATE, @visitTime visitTime, GETDATE() - 1 [GETDATE-1]
GETDATE visitTime GETDATE-1
2014-10-17 00:02:18.980 2014-10-16 23:59:59.000 2014-10-16 00:02:18.980
You'll see that the visittime date clearly falls in the range you specified as the lower bound (the -1) subtracts a whole day and not just the time part.
您会看到访问时间日期明显落在您指定的范围内，因为下限 (-1) 减去一整天，而不仅仅是时间部分。
You could use this instead:
你可以用这个代替：
-- using GETDATE() for the upper bound misses visitTime that are
-- on the current day, but at a later time than now.
WHERE visitTime < DateAdd(Day, DateDiff(Day, 0, GetDate())+1, 0)
AND visitTime >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
or if you're on SQL Server 2008+ that has adatedata type, this:
或者，如果您使用的是具有date数据类型的SQL Server 2008+，则：
WHERE CAST(visitTime AS DATE) = CAST(GETDATE() AS DATE)
Note thatGETDATE()is T-SQL specific, the ANSI equivalent isCURRENT_TIMESTAMP
请注意，这GETDATE()是特定于 T-SQL 的，ANSI 等效项是CURRENT_TIMESTAMP
回答by user353gre3
Assuming today is midnight last night to midnight tonight, you can use following condition
假设今天是昨晚午夜到今晚午夜，您可以使用以下条件
Select * from Customer where
visitTime >= DateAdd(d, Datediff(d,1, current_timestamp), 1)
and
visitTime < DateAdd(d, Datediff(d,0, current_timestamp), 1);
回答by user3203114
SELECT * FROM Customer WHERE visitTime > = convert(date,getdate())
and visitTime < dateadd(d,1,convert(date,getdate()))
回答by 100Lords
Check this out
看一下这个
select *
from Customers
where convert(date,createddate) = convert(date,getdate());
回答by Rahul Singh
Gets detail of to date only:SELECT * FROM PRODUCTION WHERE CAST(CREATIONDATE AS DATE) = CAST(GETDATE() AS DATE)
仅获取迄今为止的详细信息：SELECT * FROM PRODUCTION WHERE CAST(CREATIONDATE AS DATE) = CAST(GETDATE() AS DATE)
