How to Generate Date Ranges in Oracle SQL

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/14448

Simulation of TRUNC() in Derby

Derby is missing out a lot of functions from the set of functions that other databases usually provide. One example is the TRUNC(value, decimals) function. According to the Wikipedia, truncation can be achieved as such:

-- trunc(x, n) 
CASE WHEN x > 0 
THEN
  floor(power(10, n) * x) / power(10, n) 
ELSE 
  ceil(power(10, n) * x) / power(10, n) 
END

Unfortunately, there is no POWER(base, exponent) function in Derby either. But no problem, we can simulate that as well. Let’s consider the Wikipedia again and we’ll find:

power(b, x) = exp(x * ln(b))

If we substitute that into the original simulation, we get for Derby:

-- trunc(x, n) 
CASE WHEN x > 0 
THEN 
  floor(exp(n * ln(10)) * x) / exp(n * ln(10))
ELSE 
  ceil(exp(n * ln(10)) * x) / exp(n * ln(10)) 
END

Verbose, probably quite inefficient, but effective! Let’s run a short test, too:

create table test (x numeric(10, 5), n int);

insert into test values (11.111, 0);
insert into test values (11.111, 1);
insert into test values (11.111, 2);
insert into test values (11.111, -1);

select
  x, n, 
  case when x >= 0
  then
    floor(exp(n * ln(10)) * x) / exp(n * ln(10))
  else
    ceil(exp(n * ln(10)) * x) / exp(n * ln(10))
  end "trunc(x, n)"
from test;

The above yields

X N TRUNC(X, N)
11.111 0 11
11.111 1 11.1
11.111 2 11.11
11.111 -1 10