Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    246

    Unanswered: covert char to date

    hi all

    i have a char column which is having date in it
    while trying to convert to date its not working
    any way to covert

    for example
    select pol_dt from tpol

    1
    ----------
    2004/03/15

    pol_dt is a char column
    when we do
    select date(pol_dt) from tpol its giving
    1
    ----------
    1

    kindly suggest
    regds
    Paul

  2. #2
    Join Date
    Apr 2009
    Posts
    42
    It's easy.
    replace the '/' with '-'

    Code:
    select date(replace(pol_dt, '/', '-')) from tpol

  3. #3
    Join Date
    Oct 2007
    Posts
    246
    thax wilson for the reply
    but its giving the same o/p
    i tried
    select date(substr(pol_dt,1,4) || '-' || substr(pol_dt,5,6) || '-' ||
    substr(pol_dt,7,8)) from tbname
    its not working
    regds
    Paul

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Paul, Your SUBSTR values are incorrect for the Month and Day.
    Code:
    1234567890
    2004/03/15
    The Month starts in position 6 for 2 characters and the Day starts in position 9 for 2 characters.
    Code:
    DATE( SUBSTR(POL_DT,1,4) || '-' || 
          SUBSTR(POL_DT,6,2) || '-' ||
          SUBSTR(POL_DT,9,2) )

  5. #5
    Join Date
    Jul 2009
    Posts
    150

    Thumbs down

    Quote Originally Posted by Mathew_paul
    thax wilson for the reply
    but its giving the same o/p
    i tried
    select date(substr(pol_dt,1,4) || '-' || substr(pol_dt,5,6) || '-' ||
    substr(pol_dt,7,8)) from tbname
    its not working
    regds
    Paul
    substr(pol_dt,5,6) means substring starting from position 5 with length 6.
    Not the characters in position 5 and position 6 how you are thinking.

    Kara.

Posting Permissions

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