I've got a table that I've downloaded from an oracle database using SAS. it has a column with dates in the following format: 10/31/2012 11:00:00 AM
when I upload the data (via SQL workshop -> utilities ->data workshop -> load data) by classifying my dates as varchar2, it works fine... but fails when I select my column type to be DATE.
Please advice how to fix this issue
In your upload process, you'll need to specifically convert it to a date. This is always the case when a string is not in the default date format set for your machine. NLS_DATE_FORMAT is the parameter that determines the date format.
Converting that string to a date requires the TO_DATE function. In this case: TO_DATE(colname, 'MM/DD/YYYY HH:MI:SS AM')
can you please provide more details as how to use TO_DATE function as part of the import process... one of the option is to import dates as varchar2 and then use an sql to convert it to a date format but ideally I would like to do it as part of the import process to keep it simple
Thanks again for your assistance and sharing your experience/knowledge