I am looking for a way to filter a grid using some math on a date field. The table is from a MariaDB database and has a field called Session5 which is a date field.
I have this command in a grid:
SELECT *, concat(time_start, ' - ', time_end) AS times, concat(member.title, ' ', member.first_name, ' ', member.last_name) AS instructorname, course_schedule.course_sked_id AS Books
FROM (course_schedule course_schedule
INNER JOIN course course
ON course_schedule.course_id = course.course_id
LEFT OUTER JOIN member member
ON course_schedule.regid = member.regid )
This command in the SQL select Statement section of the grid does, of course, display all records.
I want the grid to display only those records where the field Session5 (a date field) plus 15 days is equal to or greater than todays date. In other words, if the date, session5 is more than 15 days old, I don't want the row to display. I have tried various various ways to do this, but I can't figure out a way to do it.
I have tried this format:
SELECT *, date_add(course_schedule.Session5, INTERVAL 15 day) as expire_date, concat(time_start, ' - ', time_end) AS times, concat(member.title, ' ', member.first_name, ' ', member.last_name) AS instructorname, course_schedule.course_sked_id AS Books
FROM (course_schedule course_schedule
INNER JOIN course course
ON course_schedule.course_id = course.course_id
LEFT OUTER JOIN member member
ON course_schedule.regid = member.regid )
WHERE expire_date >= now()
and I tried this:
SELECT *, concat(time_start, ' - ', time_end) AS times, concat(member.title, ' ', member.first_name, ' ', member.last_name) AS instructorname, course_schedule.course_sked_id AS Books
FROM (course_schedule course_schedule
INNER JOIN course course
ON course_schedule.course_id = course.course_id
LEFT OUTER JOIN member member
ON course_schedule.regid = member.regid )
WHERE date_add(course_schedule.Session5, INTERVAL 15 day) >= now()
I have tried setting an argument in the WHERE command, but I can't get anything to work. The MySQL date_add functions is a valid function, but native or portable syntax does not work.
Does anyone have an idea of how I can filter the rows for this grid.
Thanks
I have this command in a grid:
SELECT *, concat(time_start, ' - ', time_end) AS times, concat(member.title, ' ', member.first_name, ' ', member.last_name) AS instructorname, course_schedule.course_sked_id AS Books
FROM (course_schedule course_schedule
INNER JOIN course course
ON course_schedule.course_id = course.course_id
LEFT OUTER JOIN member member
ON course_schedule.regid = member.regid )
This command in the SQL select Statement section of the grid does, of course, display all records.
I want the grid to display only those records where the field Session5 (a date field) plus 15 days is equal to or greater than todays date. In other words, if the date, session5 is more than 15 days old, I don't want the row to display. I have tried various various ways to do this, but I can't figure out a way to do it.
I have tried this format:
SELECT *, date_add(course_schedule.Session5, INTERVAL 15 day) as expire_date, concat(time_start, ' - ', time_end) AS times, concat(member.title, ' ', member.first_name, ' ', member.last_name) AS instructorname, course_schedule.course_sked_id AS Books
FROM (course_schedule course_schedule
INNER JOIN course course
ON course_schedule.course_id = course.course_id
LEFT OUTER JOIN member member
ON course_schedule.regid = member.regid )
WHERE expire_date >= now()
and I tried this:
SELECT *, concat(time_start, ' - ', time_end) AS times, concat(member.title, ' ', member.first_name, ' ', member.last_name) AS instructorname, course_schedule.course_sked_id AS Books
FROM (course_schedule course_schedule
INNER JOIN course course
ON course_schedule.course_id = course.course_id
LEFT OUTER JOIN member member
ON course_schedule.regid = member.regid )
WHERE date_add(course_schedule.Session5, INTERVAL 15 day) >= now()
I have tried setting an argument in the WHERE command, but I can't get anything to work. The MySQL date_add functions is a valid function, but native or portable syntax does not work.
Does anyone have an idea of how I can filter the rows for this grid.
Thanks
Comment