Help with SQL filter query ticker data set

The Archives Help Topics
1

Im loving Xibo but I’m having trouble with a filter

I am using it for information screens in a High School, and I would like to display what lesson is current at any given time, the issue is that on Monday the schedule is different than the rest of the week.

I have setup a dataset with the columns LessonName, LessonStartTime, LessonEndTime, LessonSet

with the info “Lesson 1”, 9:00, 10:00, “Monday” etc for each day

i cannot get the SQL filter to work - i’d like to be able to use a filter that checks what day of the week it is and then the time range

my knowledge of SQL is limited however after searching I thought that DATE_FORMAT(NOW(), %W) would return the Weekday that I could filter against but it doesn’t show anything (if i just type in “Monday” the filter works fine…

Im successfully using the ‘meeting scheduler’ with another ticker dataset for displaying upcoming events,
using NOW() > startdate AND NOW() < enddate

any ideas on how to do this would be great

Im using windows player and version 1.7.4

2

Try putting single quotes round the %W:

DATE_FORMAT(NOW(), '%W')

3

Thanks that worked…

I also managed to the get it to display just the lesson I needed

the DataSet has 4 columns LessonName, LessonStartTime, LessonEndTime, LessonDay

with the info “Lesson 1”, 09:00:00, 10:10:00, “Monday” etc for each day

the following filter picks the current lesson for display…

LessonDay = DATE_FORMAT(NOW(), ‘%W’) AND DATE_FORMAT(NOW(), ‘%T’) > tkLessonStartTime AND DATE_FORMAT(NOW(), ‘%T’) < tkLessonEndTime

the only hiccup was I needed to ensure the times entered were in 24h hh:mm:ss format and all good

hopefully others will find this useful