SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Get Top 1 ad. based on current time-1 hour advance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ram.developer
Starting Member

India
3 Posts

Posted - 05/05/2014 :  13:16:45  Show Profile  Reply with Quote
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

Edited by - ram.developer on 05/05/2014 13:35:50

kostya1122
Starting Member

10 Posts

Posted - 05/05/2014 :  19:41:06  Show Profile  Reply with Quote
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

India
3 Posts

Posted - 05/06/2014 :  15:27:30  Show Profile  Reply with Quote
thank you very much KOSTYANTYN YERMOLENKO its working fine..

Ram
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000