CODEWITHSUNDEEP
sql-serversql-server-2008
Hitesh
Hitesh

Reputation: 1228

How to get all data which time is in range to next 2 hours from current time in SQL

I have a table tblSMS with a datetime and time column (and some other columns).

I get all reminder within next 2 hours on dashboard so I want to get data from table where datetime is today and time is within the next 2 hours.

I used this stored procedure, but it is not working:

select * 
from tblSMS 
where  
    SMSTime >= (SELECT Convert(varchar(5), GetDate(), 108)) 
    and (SMSTime <= (SELECT Convert(varchar(5), (Select dateadd (hour, 2, getdate())), 108)))
    and Convert(varchar(10), SMSDate, 110) = Convert(varchar(10), GETDATE(), 110)

Thanks

Upvotes: 0

Views: 674

Answers (1)

uzi
uzi

Reputation: 4146

No need to use time column if you are storing in proper datetime format. It's better to make comparisons in datetime format. Otherwise you may have troubles if next 2 hours falls on the next day.

select * 
from tblSMS 
where  
    SMSDate between getdate() and dateadd(hh, 2, getdate())

Combine two columns to get datetime if you are storing SMSDate as a date

select * 
from tblSMS 
where  
    SMSDate + cast(SMSTime as datetime) between getdate() and dateadd(hh, 2, getdate())

Upvotes: 1

Related Questions