Please help me to write a query (Date)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Please help me to write a query (Date)

  1. #1
    Join Date
    Dec 2005
    Posts
    1

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Dates don't have any inherent format -- they are converted to strings in some display format either based on the settings of your client, or your server, or through a specified date format picture.

    If you want to view them in a particular format then try
    Code:
    alter session set nls_date_format = 'dd-mon-yyyy'
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jan 2006
    Posts
    2
    Hi,
    You can try this. Hope this solution fills u r requirements.

    SELECT TO_CHAR(TO_DATE('01-JAN-2001','DD-MON-YYYY') + (ROWNUM - 1),'dd-MON-YYYY') new_date
    FROM ALL_OBJECTS
    WHERE TO_DATE('01-JAN-2001','DD-MON-YYYY') + (ROWNUM - 1) <=
    TO_DATE('16-MAY-2001','DD-MON-YYYY');


    Thanks & Regards
    Vinod Kumar K

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width