DATE CONVERSION PROBLEM

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...

Answer 1

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

 

Answer 2

User-347061861 posted

Not  a Valid Month error is coming?

what should I do?

Answer 3

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?

Answer 4

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.