SQL - Select Records after current date/time

Question

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).

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.

So on the 05/07/11 at 19:00 it should show:

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:

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?

ALSO Date is generated within VB.Net, so would the time. Current sql (and working) code is:

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') 

Answer 1

Adding columns Date and Time and the compare against the interval -2 hours +3 years.

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.

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())

Answer 2

If you use the databases time and date fields, you may simple compare to current date/time

…
WHERE `Date` > NOW()
AND `Time` > NOW()

Answer 3

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.