Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get Top 1 ad. based on current time-1 hour advance

Author  Topic 

ram.developer
Starting Member

3 Posts

Posted - 2014-05-05 : 13:16:45
Friends,
I am new for SQL SP
I have legacy table, so the table structure is little bit complex as date and ad starttime and end time as maintained 3 different column, please find my table structure below

Table 1 - Advertisement

AdID AdName AdDate AdStartTime AdEndTime
1 Ad1 2014-05-05 8:30 AM 10:00 AM
2 Ad2 2014-05-11 11:30AM 1:30 PM
3 Ad3 2014-05-05 5:00 AM 6:30 AM
4 Ad4 2014-05-12 4:30 PM 7:30 PM
5 Ad5 2014-05-05 11:30 PM 12:30 PM

On Executing this SP i should get record by Currentdate and one hour before the present time.

Assume, Currentdate = 2014-05-05 & Present Time is greater than 7.30AM and not more than 10:00 Am


Expected Result Table

AdID AdName AdDate AdStartTime AdEndTime
1 Ad1 2014-05-05 8:30 AM 10:00 AM

Another example if the today's date is 2014-05-05 and the current time is greater than 4:00 Am and not more than 6:30 Am we should retrive below record

Expected Result Table

AdID AdName AdDate AdStartTime AdEndTime

3 Ad3 2014-05-05 05:00 Am 06:30 Am

Please help me to get the expected result for the above scenario


Ram

kostya1122
Starting Member

15 Posts

Posted - 2014-05-05 : 19:41:06
add where clause like
where
dateadd(hh,+1,cast(getdate() as time(0))) between AdStartTime
and AdEndTime
Go to Top of Page

ram.developer
Starting Member

3 Posts

Posted - 2014-05-06 : 15:27:30
thank you very much KOSTYANTYN YERMOLENKO its working fine..

Ram
Go to Top of Page
   

- Advertisement -