HELP! What show is on the air query

Results 1 to 3 of 3

Thread: HELP! What show is on the air query

  1. #1
    Join Date
    Dec 1969

    Default HELP! What show is on the air query

    Ok, i&#039;ve been wrestling with this query all night long. I just can&#039;t figure it out. I really need some help ASAP.<BR><BR><BR> I&#039;ve got the following related date/time information stored about shows in tblshows that defines when a show is on the air. This schedule is same everyweek.<BR><BR>day_start - (Stored as a &#039;Sunday&#039;, &#039;Monday&#039; etc..)<BR>day_start_int - (stored as 1, 2, 3, etc...)<BR>day_span - (stored as an int that defines how many days this show runs. (if it starts on sunday and has a day span of 4, then it&#039;s also on at the same time on monday, tuesday, and wed.)<BR>time_start - (stored as 1:00PM)<BR>time_end - (stored as 1:00PM)<BR><BR><BR>I need to be able to ask the database this simple question.<BR>What show is on the air right now?<BR><BR>Please help, email me directly at (remove the nospam). Thanks<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: HELP! What show is on the air query

    I don&#039;t tend to email solutions to people, because that&#039;s not the idea of a msgboard.<BR><BR>The following solution is for SQL Server:<BR><BR>SELECT program FROM #show<BR>WHERE DATEPART(dw,@CurrentDateTime) BETWEEN day_start_int and day_start_int + day_span<BR>AND CONVERT(varchar(10),@CurrentDateTime,108) BETWEEN CONVERT(varchar(10),time_start,108) AND CONVERT(varchar(10),time_end,108)<BR><BR>@CurrentD ateTime is the variable which holds the (guess what) current date and time.<BR><BR>Basically you need to do: (pseudo code)<BR><BR>select program from the table<BR>where [current day of week] is between day_start_int and day_start_int + day_span<BR>and the timepart from currentdatetime is between start and endtime<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default Alternately...

    If you want to know what&#039;s on now...<BR><BR>SELECT ShowDetails<BR>FROM ShowTable<BR>WHERE DateDiff(minute,ShowTimeStart,getdate()) = MIN(DateDiff(minute,ShowTimeStart,getdate)<BR>AND ShowTime &#062; GetDate()<BR><BR>n&#039;est-ce pas?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts