Thanks for your suggestion.

Somehow i managed to write query in Oracle.

Following is my query.

WITH years AS (

SELECT ROWNUM rn

FROM dual

CONNECT BY LEVEL <= (select round( SYSDATE-to_date('2010-01-01','YYYY-MM-DD')) from dual))

Select

to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn ,'yyyy') * 10000 + to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'mm') * 100 + to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'dd') As Date_ID

,to_date('2010-01-01','YYYY-MM-DD')-1 +rn As "Date"

,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn ,'yyyy') As Year

,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'MM') As Month_Number

,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Month') As Month

,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'dd') As DayOfMonth

,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Day') As DayOfWeek

,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'D') As DayOfWeekNo

,to_char(to_date('2010-01-01','YYYY-MM-DD')-1 +rn,'Q') As Quarter

From years;

If you run this query you will see my expected output.

Now my concern is i don't want to hard code date. In above query i have hard coded the date '2010-01-01'.

Can we somehow use it from variable?