get dynamic date range

get-dynamic-date-range.sql

-- Pure SQL way to get dynamic date range instead of pipelined function
-- as shown here: https://twitter.com/cczarski/status/1500830480358330370
with
    data as (
        select 
            level insert_order,
            decode(level, 
                1, 'Since Yesterday', 
                2, 'Last Week',
                3, 'Last Month', 
                4, 'Last Year',
                5, 'Older than Last Year'
            ) disp,
            sysdate - decode(level,
                1, 1,
                2, 7,
                3, 30,
                4, 365,
                5, null) from_date,
            sysdate - decode(level,
                1, null,
                2, 1,
                3, 7,
                4, 30,
                5, 365
            ) to_date,
            'YYYYMMDDHH24MISS' date_format
        from sys.dual
        connect by level <= 5
    )
select
    d.insert_order,
    d.disp,
    to_char(trunc(d.from_date), d.date_format)
        || '|'
        || to_char(trunc(d.to_date), d.date_format) val
from data d