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!

Convert string to date

Discussion in 'SQL PL/SQL' started by Maddy, Feb 24, 2016.

  1. Maddy

    Maddy Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA
    Hi gurus,

    Please help with below requirement

    CREATE TABLE z_test (date_INTERVAL VARCHAR2 (1000));



    SET DEFINE OFF;

    INSERT INTO Z_TEST (date_INTERVAL)
    VALUES ('sysdate');

    COMMIT;

    I tried below SQl:

    SELECT to_date(xx,'mm/dd/yyyy')
    FROM (SELECT date_INTERVAL xx FROM z_test);

    I getting error as:
    ORA-01858: a non-numeric character was found where a numeric was expected




    output:
    2/24/2016
     
  2. shareefirfan

    shareefirfan Forum Advisor

    Messages:
    24
    Likes Received:
    8
    Trophy Points:
    485
    Location:
    Hindupur, AP, India
    Try this...

    1. CREATE TABLE z_test (date_INTERVAL VARCHAR2 (1000));

    2. INSERT INTO Z_TEST (date_INTERVAL) VALUES (sysdate);
    COMMIT;

    3. SELECT to_char(to_date(date_INTERVAL,'dd-mon-rrrr'), 'mm/dd/rrrr') xx FROM z_test;
     
    Maddy likes this.
  3. Maddy

    Maddy Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA
    Thanks for the quick reply . its working for all cases :)
     
    Last edited: Feb 24, 2016
  4. Maddy

    Maddy Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA

    one more question

    i want date_interval column to be 'string field'. i am getting date in the filed.which is not right. i want to use that column for every month to run first monday of the month.

    create TABLE z_test1 (date_INTERVAL VARCHAR2 (1000));

    INSERT INTO Z_TEST1 (date_INTERVAL) VALUES ( 'NEXT_DAY(add_months(last_day(sysdate),-1),''MONDAY'')');
    COMMIT;
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are approaching this in the wrong way -- you want dates to be DATES, not strings. You can convert a valid date to a string of characters at any time using TO_CHAR() then compare that. Making dates into strings can cause all sorts of issues; read here:

    https://dfitzjarrell.wordpress.com/2016/02/17/that-aint-right/
     
    Maddy likes this.
  6. Maddy

    Maddy Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    INDIA

    Thanks for the information
     
  7. Siddhartha

    Siddhartha Active Member

    Messages:
    16
    Likes Received:
    2
    Trophy Points:
    90
    Location:
    Bangalore
    SELECT to_char(to_date(date_INTERVAL,'dd-mon-rrrr'), 'dd/mm/yyyy') sid FROM z_test;