Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
get dynamic date range
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment