Please help me to write a query (Date)
Hello All,
I want to insert all dates between two dates in a table (oracle 10g). The target table is.
CREATE TABLE SEN_DATE (CALENDAR_DATE DATE, DAY NUMBER(3),DAY_NAME VARCHAR2(10), MONTH NUMBER(3),MONTH_NAME VARCHAR2(10),YEAR nuMBER(7));
To get dates between two dates I have used the following query.
SELECT TO_DATE('01-JAN-2001','DD-MON-YYYY') + (ROWNUM - 1) new_date
FROM ALL_OBJECTS
WHERE TO_DATE('01-JAN-2001','DD-MON-YYYY') + (ROWNUM - 1) <=
TO_DATE('16-MAY-2001','DD-MON-YYYY');
The above query works fine and return all the dates, but the format is 1/1/2001, 1/2/2001... and so on till 05/16/2001. But I want to insert the records in the target table dd-mon-yyyy format in the date format and other relevant information like month number, month name, day_number, day_name based on the target table (Sen_date).
Please help to write the query.
Thanks in advance,
Senthil