Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, bhavya.

Asked: August 03, 2017 - 10:08 pm UTC

Last updated: August 05, 2017 - 9:20 pm UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

Hello,
I am trying to insert data into a table, The only thing is it is of 20 years.
I have already created a query. The query is in a good shape but the only thing missing in my query is the dates. Below is my query.
I want LV_START_DATE as 2010-01-01 and LV_END_DATE as 2031-01-01. I don't know how to give values for this 2 columns in the query itself.

Any help is appreciated. Thanks.

(SELECT
GENERATED_PERIOD_START AS REPORT_DATE,
CASE WHEN WEEKDAY(GENERATED_PERIOD_START) > 4 THEN
ADD_DAYS(GENERATED_PERIOD_START, WEEKDAY(GENERATED_PERIOD_START) +
CASE WHEN WEEKDAY(GENERATED_PERIOD_START)=5 THEN 1 ELSE -1 END)
ELSE
ADD_DAYS(GENERATED_PERIOD_START, 4-WEEKDAY(GENERATED_PERIOD_START))
END AS WEEK_END
FROM SERIES_GENERATE_DATE ( 'INTERVAL 1 DAY', :LV_START_DATE, COALESCE(:LV_END_DATE, CURRENT_DATE)) ;


I want to generate rows being 1 per day between lv_start and lv_end.
I am looking from 2010-01-01 to 2031-01-01. Thanks.



and Connor said...



will walk you through the process.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.