locked
DATE CONVERSION PROBLEM RRS feed

  • Question

  • User-347061861 posted

    DEAR ALL...

    I HAVE A TABLE IN ORACLE DATABASE..I MIGRATED ACCESS DATA INTO ORACLE DATABASE. I HAVE ONE COLUMN WHOSE DATATYPE IS VARCHAR2 AND IT has  DATE IN 21JAN2011 FORMAT...BUT I M NOT ABLE TO CONVERT IT TO DD/MM/YYYY FORMAT...WHAT CAN I DO TO GET LAST 10 DAYS RECORD...

    Saturday, November 19, 2011 11:00 AM

All replies

  • User269602965 posted

    You would like to get (FILTER) the last 10 days of records from a column that has a datatype of VARCHAR2, but contains a string representing a date in the format of DDMONYYYY without separators between days, months, and year.

    Your FILTER records by a WHERE CLAUSE

    I do not know the name of the VARCHAR2 column, so let's call it DATE_STRING_COLUMN

    SELECT * FROM {YourTableName} WHERE SYSDATE - TO_DATE(DATE_STRING_COLUMN,'DDMONYYYY') < 11;

    Will return the last 10 days of records based on the string date in the DATE_STRING_COLUMN

     

    Saturday, November 19, 2011 4:57 PM
  • User-347061861 posted

    Not  a Valid Month error is coming?

    what should I do?

    Saturday, November 19, 2011 11:41 PM
  • User-347061861 posted

    i found some records in bad format like sept not sep...so i exported data in excel and converted it into mm/dd/yyyy format...can i export this data in oracle ? will it work? how to export through toad..any idea?

    Saturday, November 19, 2011 11:49 PM
  • User269602965 posted

    you can load Excel spreadsheets using TOAD.

    You first create an empty table in the data schema.

    For date columns use the Oracle datatype DATE

    Then you can select the Import Data option for the table.

    And pick EXCEL and browse for the file.

    In the TOAD GUI to import the data using Excel, you can then specify the date format dd/mm/yyyy.

    yes, you must have valid dates to avoid errors.

     

    Sunday, November 20, 2011 6:03 PM