SQL - select records after current date / time

Question

So I have a table 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 

and etc.

Currently, I have one rule according to my WHERE expression, which shows all days between today (so it will be 05/07/11 until the same date 3 years later 05/07/14).

I would also like to add another rule in the WHERE clause so that it only shows the time (when the current date is equal to the date in the table) two hours before the current time.

So, on 05/07/11 at 19:00 he 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 that day, now he must show:

 Date Time Field3 Field4 - etc. -------------------------------------------------- 08/07/11 17:30 08/07/11 19:00 09/07/11 19:00 

How would this be done in my SQL? I think that it should be if then or the case when then there was a statement, but I could not process it?

SEE ALSO Date is generated internally by VB.Net, as is time. Current sql code (and working):

 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 comparison with an interval of -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 can try this. This can improve performance using the 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 database time and date fields, you can simply compare with the current date / time

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

Answer 3

I would do this by joining the second request. Sort of:

  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, there is a “Show” column in the subquery that you can use in your CASE statement, and it does your filtering.