CODEWITHSUNDEEP
sqloracle-database
RLearner
RLearner

Reputation: 65

Generate a table with interval of months in a year -Oracle

I have to create a table in the below format:-

TS_RANGE_BEGIN      TS_RANGE_END    
2019-01-01 17:00:00 2019-01-31 17:00:00  
2019-02-01 17:00:00 2019-02-28 17:00:00  
2019-03-01 17:00:00 2019-03-31 17:00:00

Could you please help on this?

Thanks,

Upvotes: 0

Views: 43

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

Looks like a simple row generator:

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> with std (datum) as
  2    (select to_date('01.01.2019 17:00', 'dd.mm.yyyy hh24:mi') from dual)
  3  select add_months(datum, level - 1) ts_range_begin,
  4         add_months(datum, level) - 1 ts_range_end
  5  from std
  6  connect by level <= 12;

TS_RANGE_BEGIN      TS_RANGE_END
------------------- -------------------
2019-01-01 17:00:00 2019-01-31 17:00:00
2019-02-01 17:00:00 2019-02-28 17:00:00
2019-03-01 17:00:00 2019-03-31 17:00:00
2019-04-01 17:00:00 2019-04-30 17:00:00
2019-05-01 17:00:00 2019-05-31 17:00:00
2019-06-01 17:00:00 2019-06-30 17:00:00
2019-07-01 17:00:00 2019-07-31 17:00:00
2019-08-01 17:00:00 2019-08-31 17:00:00
2019-09-01 17:00:00 2019-09-30 17:00:00
2019-10-01 17:00:00 2019-10-31 17:00:00
2019-11-01 17:00:00 2019-11-30 17:00:00
2019-12-01 17:00:00 2019-12-31 17:00:00

12 rows selected.

SQL>

The STD CTE is used to set starting date.

Upvotes: 3

Related Questions