Improving Date Filtering

Grocery CRUD Community Edition
1

Hello,

I have generated a datagrid with a few date columns. To filter a single date works fine with a pop-up calendar to assist with formatting.

I am looking to improve this filtering by adding DATE RANGE filtering. I have generated procedure and call in MySQL 5.x, that gives me a start and end date based on a select option:

DELIMITER //

CREATE PROCEDURE get_date_range(
    IN range_type VARCHAR(20),
    OUT start_date DATE,
    OUT end_date DATE
)
BEGIN
    DECLARE today DATE;
    SET today = CURDATE();

    CASE range_type
        WHEN 'today' THEN
            SET start_date = today;
            SET end_date = today;

        WHEN 'yesterday' THEN
            SET start_date = DATE_SUB(today, INTERVAL 1 DAY);
            SET end_date = start_date;

        WHEN 'tomorrow' THEN
            SET start_date = DATE_ADD(today, INTERVAL 1 DAY);
            SET end_date = start_date;

        WHEN 'this week' THEN
            SET start_date = DATE_SUB(today, INTERVAL WEEKDAY(today) DAY);
            SET end_date = DATE_ADD(start_date, INTERVAL 6 DAY);

        WHEN 'this week to date' THEN
            SET start_date = DATE_SUB(today, INTERVAL WEEKDAY(today) DAY);
            SET end_date = today;

        WHEN 'last week' THEN
            SET start_date = DATE_SUB(DATE_SUB(today, INTERVAL WEEKDAY(today) DAY), INTERVAL 7 DAY);
            SET end_date = DATE_ADD(start_date, INTERVAL 6 DAY);

        WHEN 'next week' THEN
            SET start_date = DATE_ADD(DATE_SUB(today, INTERVAL WEEKDAY(today) DAY), INTERVAL 7 DAY);
            SET end_date = DATE_ADD(start_date, INTERVAL 6 DAY);

        WHEN 'this month' THEN
            SET start_date = DATE_SUB(today, INTERVAL DAYOFMONTH(today) - 1 DAY);
            SET end_date = LAST_DAY(today);

        WHEN 'this month to date' THEN
            SET start_date = DATE_SUB(today, INTERVAL DAYOFMONTH(today) - 1 DAY);
            SET end_date = today;

        WHEN 'last month' THEN
            SET start_date = DATE_SUB(DATE_SUB(today, INTERVAL DAYOFMONTH(today) - 1 DAY), INTERVAL 1 MONTH);
            SET end_date = LAST_DAY(start_date);

        WHEN 'next month' THEN
            SET start_date = DATE_ADD(DATE_SUB(today, INTERVAL DAYOFMONTH(today) - 1 DAY), INTERVAL 1 MONTH);
            SET end_date = LAST_DAY(start_date);

        WHEN 'last 30 days' THEN
            SET start_date = DATE_SUB(today, INTERVAL 30 DAY);
            SET end_date = today;

        WHEN 'last 60 days' THEN
            SET start_date = DATE_SUB(today, INTERVAL 60 DAY);
            SET end_date = today;

        WHEN 'last 90 days' THEN
            SET start_date = DATE_SUB(today, INTERVAL 90 DAY);
            SET end_date = today;

        WHEN 'this year' THEN
            SET start_date = DATE_SUB(today, INTERVAL DAYOFYEAR(today) - 1 DAY);
            SET end_date = DATE_ADD(start_date, INTERVAL 365 DAY);

        WHEN 'this year to date' THEN
            SET start_date = DATE_SUB(today, INTERVAL DAYOFYEAR(today) - 1 DAY);
            SET end_date = today;

        WHEN 'last year' THEN
            SET start_date = DATE_SUB(DATE_SUB(today, INTERVAL DAYOFYEAR(today) - 1 DAY), INTERVAL 1 YEAR);
            SET end_date = DATE_ADD(start_date, INTERVAL 365 DAY);

        WHEN 'next year' THEN
            SET start_date = DATE_ADD(DATE_SUB(today, INTERVAL DAYOFYEAR(today) - 1 DAY), INTERVAL 1 YEAR);
            SET end_date = DATE_ADD(start_date, INTERVAL 365 DAY);

        ELSE
            SET start_date = NULL;
            SET end_date = NULL;
    END CASE;
END //

DELIMITER ;


CALL get_date_range('this year to date', @start_date, @end_date);
SELECT @start_date AS start_date, @end_date AS end_date;

The goal is to show a select menu of the options (with CUSTOM RANGE - to manually enter start and dates), then it produces the proper date range filtered set.

Anyone ever done this? Feedback would be appreciated.