Get Top 1 ad. based on current time-1 hour advance

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

ram.developer
Starting Member

3 Posts

Posted - 2014-05-06 : 15:27:30

thank you very much KOSTYANTYN YERMOLENKO its working fine..

Ram

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources