Convert number to Date format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Convert number to Date format

Hybrid View

  1. #1
    Join Date
    Aug 2001
    Posts
    30

    Convert number to Date format

    Hi I have stored no. of seconds from 1970 in number datatype on a table and I need to access this number and display it in Date format mm/dd/yyyy using PL/SQL.
    Can anyone help?
    Sitaram KUlkarni

  2. #2
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    VERY easy to do, IF you know date arithmetic in Oracle :

    --
    -- Convert Oracle date to number of seconds since reference date MDP
    --
    select to_number(sysdate - to_date('01-JAN-1970','DD-MON-YYYY'))
    * (24 * 60 * 60) "C Time format"
    from sys.dual
    ;
    --
    -- Convert from number of seconds since reference date to Oracle date
    --
    select to_char(to_date('01-JAN-1970','DD-MON-YYYY') +
    ( &no_sec_since_70 / (60 * 60 * 24) ),'DD-MON-YYYY HH24:MI:SS')
    "New Date"
    from sys.dual
    ;


    Regards,

    Chiappa

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