1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

date to number conversion in oracle

Discussion in 'SQL PL/SQL' started by prabhur, Apr 29, 2015.

  1. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    I have two tables tab1, tab2. In tab1 I have column with date datatype. date value stored in tab1 like this '25-APR-2011'. In tab2 I have column ex_date with number datatype.
    I want to convert date in tab1 to number and store it in ex_date column in tab2.

    Input
    '25-APR-2011'

    output

    25042011

    help to solve the task
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Check below query, then you will get an Idea

    select to_char(sysdate,'ddmmyyyy') from dual
     
  3. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    HI,
    I WANT 'DD-MON-YYYY' FORMAT TO BE CONVERT INTO NUMBER

    With regards
    Prabhu
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    select to_char(to_date('01-JAN-2015','DD-MON-YYYY'),'DDMMYYYY') from dual;

    instead of to_date('01-JAN-2015','DD-MON-YYYY') you can use your column
     
  5. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi
    This is working fine. my except result is different.

    if 24-Apr-2012 then it is generating some serial number like this 1220479200

    help me find the solution.
     
  6. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    HI,
    My input date is

    29-APR-2015

    output number

    1430298000 this
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    I didn't get you.

    Plz post sample data and required output clearly.
     
  8. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    1430298000 - it's unix timestamp for 29-APR-2015.
     
  9. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    When I convert today's report date it is storing this serial number

    eg

    input -29-APR-2015

    output is 1430298000

    what sql query for it
     
  10. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    SELECT (sysdate - to_date(01-Jan-1970,'DD-MON-YYYY')) * (86400) as dt FROM dual

    output timestamp can be produced by the query
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    No, it isn't ever stored in that format. It's stored in Oracle's proprietary date format. What you're seeing is the DISPLAY format which has NOTHING to do with how it's stored.



    Why?? Why on earth would you store a DATE as a NUMBER? That defeats the purpose of using dates in your data.




    As I said before this defeats the purpose of having dates in the data. Had you looked in the documentation or on google.com (Google is your friend) you'd have found your answer. Basically you need to convert the DATE to a STRING then convert THAT to a NUMBER:


    Code (SQL):

    SQL> SELECT to_number(to_char(sysdate, 'DDMMRRRR')) FROM dual;


    TO_NUMBER(TO_CHAR(SYSDATE,'DDMMRRRR'))
    --------------------------------------
                                  29042015
    SQL>


     

    As you see it's a very simple process. Still you should SERIOUSLY reconsider storing any date values in NUMBER columns.
     
    prabhur likes this.
  12. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    Thank u . I got except answer.
    With Regards,
    Prabhu
     
  13. Sateesh.chandra90

    Sateesh.chandra90 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    select to_char(to_date('01-APR-2015','DD-MON-YYYY'),'DDMMYYYY') from dual;

    result is :01042015