This date dimension table, how to use the stored procedure loop inserts, date up to 2030-CodePudding

CodePudding user response:

Is there a man, for help

CodePudding user response:

 
Select the date '2016-06-29' + ddate rownum - 1, 
Trunc (trunc (the date '2016-06-29' rownum + 1), 'mm) monstart, 
Trunc (last_day (the date '2016-06-29') + rownum - 1) monend 
The from dual 
Connect by rownum & lt; The date '2030-12-31' - the date '2016-06-29' + 1;

CodePudding user response:

refer to the second floor wmxcn2000 response:

 
Select the date '2016-06-29' + ddate rownum - 1, 
Trunc (trunc (the date '2016-06-29' rownum + 1), 'mm) monstart, 
Trunc (last_day (the date '2016-06-29') + rownum - 1) monend 
The from dual 
Connect by rownum & lt; The date '2030-12-31' - the date '2016-06-29' + 1;

Thank you, but how did it come the query results, inserted into the list, this Zhang Weidu table has been used in the future,

CodePudding user response:

 
Create table test (ddate date, monstart date, monend date); 

Insert into test (ddate, monstart monend) 
Select the date '2016-06-29' + ddate rownum - 1, 
Trunc (trunc (the date '2016-06-29' rownum + 1), 'mm) monstart, 
Trunc (last_day (the date '2016-06-29') + rownum - 1) monend 
The from dual 
Connect by rownum & lt; The date '2030-12-31' - the date '2016-06-29' + 1;

CodePudding user response:

refer to the second floor wmxcn2000 response:

 
Select the date '2016-06-29' + ddate rownum - 1, 
Trunc (trunc (the date '2016-06-29' rownum + 1), 'mm) monstart, 
Trunc (last_day (the date '2016-06-29') + rownum - 1) monend 
The from dual 
Connect by rownum & lt; The date '2030-12-31' - the date '2016-06-29' + 1;

Can also be replaced by level, in the form of

 create table test (dtime date, dt_start date, dt_end date); 

Insert into test (dtime, dt_start dt_end) 
With TMP as 
(select to_date (' 2016-06-29 ', '- dd yyyy - mm) dtime from dual) 
The select dtime + level 1 dtime, 
Trunc (trunc (dtime) + level 1, 'mm) dt_start, 
Trunc (last_day (dtime + level - 1)) dt_end, 
The from TMP 
Connect by level & lt;=the date '2030-12-31' - dtime + 1;