I’ve just come across an interesting SQL question here on
CodeRanch. How to generate date ranges in SQL, given any input date. The question didn’t specify the SQL dialect, so let’s choose Oracle SQL, which features the awesome CONNECT BY clause. The requirements specify that given any input date:
- Date ranges span 12 months
- The first date range starts at the input date
- The last date range includes today
- Subsequent date ranges repeat the same date
For example, using 2010-06-10 we would get:
START_DATE END_DATE
-----------------------
2010-06-10 2011-06-10
2011-06-10 2012-06-10
2012-06-10 2013-06-10
2013-06-10 2014-06-10 <-- This includes today, 2013-07-24
It’s actually very simple:
SELECT
add_months(input, (level - 1) * 12) start_date,
add_months(input, level * 12) end_date
FROM (
-- Set the input date here
SELECT DATE '2010-06-10' input
FROM DUAL
)
CONNECT BY
add_months(input, (level - 1) * 12) < sysdate
See the SQL Fiddle here to see the above in action:
http://sqlfiddle.com/#!4/d41d8/14448Like this:
Like Loading...
Published by lukaseder
I made jOOQ
View all posts by lukaseder
oracle 12c