SQL - 选择当前日期/时间之后的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6582373/
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 - Select Records after current date/time
提问by user829634
So I have a table set out below
所以我在下面列出了一张表格
Date Time Field3 Field4 - etc.
--------------------------------------------------
05/07/11 17:45 blah blah
05/07/11 19:45 blah blah
08/07/11 17:30
08/07/11 19:00
09/07/11 19:00
etc.
等等。
I currently have one rule in under my WHERE Statement so that is shows all days between today (so it would be 05/07/11 until the same date 3 years later 05/07/14).
我目前在我的 WHERE 声明下有一个规则，所以它显示了今天之间的所有日子（所以它会是 05/07/11 直到 3 年后的同一日期 05/07/14）。
I would also like to add another rule under the WHERE Statement so that it only shows times (when the current date is equal to the date in the table) two hours before the current time.
我还想在 WHERE 语句下添加另一个规则，以便它只显示当前时间前两小时的时间（当当前日期等于表中的日期时）。
So on the 05/07/11 at 19:00 it should show:
所以在 05/07/11 在 19:00 它应该显示：
Date Time Field3 Field4 - etc.
--------------------------------------------------
05/07/11 17:45 blah blah
05/07/11 19:45 blah blah
08/07/11 17:30
08/07/11 19:00
09/07/11 19:00
at 21:46 on the same day, it should now show:
在同一天的 21:46，它现在应该显示：
Date Time Field3 Field4 - etc.
--------------------------------------------------
08/07/11 17:30
08/07/11 19:00
09/07/11 19:00
How would I do this in my SQL? I'm thinking it'd have to be an if then or case when then statement, but i havent been able to work it out?
我将如何在我的 SQL 中执行此操作？我认为它必须是 if then 或 case when then 语句，但我无法解决？
ALSO Date is generated within VB.Net, so would the time. Current sql (and working) code is:
也日期是在 VB.Net 中生成的，时间也是如此。当前的 sql（和工作）代码是：
SELECT m.MatchID Manage, m.Date, m.Time, t.TeamCode "Home", b.TeamCode "Away",
g.GroundName "Ground", ( SUBSTRING(u.GivenName,1,1) + '. ' + RTRIM(u.Surname) ) AS Referee,
( SUBSTRING(v.GivenName,1,1) + '. ' + RTRIM(v.Surname) ) AS "Assistant 1",
( SUBSTRING(w.GivenName,1,1) + '. ' + RTRIM(w.Surname) ) AS "Assistant 2",
a.FOfficialID, a.AssessorID, a.RefereeAID, a.AReferee1AID, a.AReferee2AID,
a.FOfficialAID, a.AssessorAID, 'Details' "Details", t.AgeGroupID, r.WetWeatherID
FROM Match m
LEFT OUTER JOIN Appointment a ON m.MatchID=a.MatchID
LEFT OUTER JOIN WetWeather r ON r.MatchID=m.MatchID
INNER JOIN Team t ON m.HomeTeamID=t.TeamID
INNER JOIN Team b ON m.AwayTeamID=b.TeamID
INNER JOIN Ground g ON g.GroundID=m.GroundID
LEFT OUTER JOIN Users u ON u.UserID=a.RefereeID
LEFT OUTER JOIN Users v on v.UserID=a.AReferee1ID
LEFT OUTER JOIN Users w on w.UserID=a.AReferee2ID
WHERE (m.Date BETWEEN '05-Jul-2011' AND '05-Jul-2014')
采纳答案by Mikael Eriksson
Adding columns Date and Time and the compare against the interval -2 hours +3 years.
添加日期和时间列，并与 -2 小时 +3 年的时间间隔进行比较。
select *
from YourTable
where Date+cast(Time as datetime) between dateadd(hour, -2, getdate()) and dateadd(year, 3, getdate())
Not sure if any index on Date can be used in the above query. If you have performance issues you could try this instead. It might do a better job using a Date index.
不确定是否可以在上述查询中使用 Date 上的任何索引。如果你有性能问题，你可以试试这个。使用日期索引可能会做得更好。
select *
from YourTable
where Date between cast(getdate() as date) and dateadd(year, 3, getdate()) and
Date+cast(Time as datetime) > dateadd(hour, -2, getdate())
回答by feeela
If you use the databases time and date fields, you may simple compare to current date/time
如果您使用数据库时间和日期字段，您可以简单地与当前日期/时间进行比较
…
WHERE `Date` > NOW()
AND `Time` > NOW()
回答by JBrooks
I would do it by joining to a sub query. Something like:
我会通过加入子查询来做到这一点。就像是：
select ...
from Match as m
inner join
(select mSub.MatchID,
case when Date+cast(Time as datetime) between dateadd(hour,-2,getdate())
and getdate() then 1 else 0 end as Show
from Match as mSub
where Date between
cast(getdate() as date)
and cast(dateadd(year,3,getdate()) as date)
) as Control
on Control.MatchID=m.MatchID
So the sub query gives you your Show column that you can use in your CASE statement and it does your filtering.
因此，子查询为您提供了您可以在 CASE 语句中使用的 Show 列，并进行过滤。
