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!

Need to convert or display Varchar2 into Date

Discussion in 'SQL PL/SQL' started by jagadekara, May 7, 2015.

  1. jagadekara

    jagadekara Forum Guru

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

    I have a table XX_TEST and have XXCOL1 which is varchar2 data type. It has value like 2011/12/23 00:00:00

    No I want to display it as 23-DEC-2011 in out put.
     
  2. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Code (SQL):

    SELECT to_char(to_date(XXCOL1, 'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-YYYY') output
    FROM XX_TEST
     
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Got It.

    Thanks Eras.
     
  4. shemo

    shemo Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    to_char(To_date(XXCOL1,'dd-mon-yyyy'),'dd-Mon-yyyy')
     
  5. jagadekara

    jagadekara Forum Guru

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

    Your's giving me error.
     
  6. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello Jagadekara,

    What if the column XXCOL1 contains character values . ?
     
  7. jagadekara

    jagadekara Forum Guru

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

    Don't know what to do in that situation. But no problem for me, because while inserting in to that table we have FND_STANDARD_DATE value set. So we are giving only 2011/12/23 00:00:00 these type of values.

    Thanks.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    WHY are you storing date/time data in a varchar2 field?
     
  9. jagadekara

    jagadekara Forum Guru

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

    It's a DFF. So it is storing in one of the Attribute. That is po_headers_all standard table. It is varchar2 data type.
     
  10. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Doesn't the provided retrieval wrapper do the format conversion for you? Or are you <gasp> accessing the table directly via SQL? ;-)