Oracle date format issue

Question

I'm trying to export/import data in .csv format using SQLDeveloper. The source and destination databases are Oracle 11g. I'm having a hard time with the date formats. In the exported .csv, I see dates like:

31-AUG-09 11.54.00.000000000 AM 

I'm trying to figure out the appropriate format string, but I don't know what the last element is before the meridian indicator (AM/PM). Here's the format string I have.

'DD-MON-YY HH.MI.SS.??????????? AM'

What should take the place of the question marks?

Answer 1

If these values are always 00000000000, then ??????????? could be just fine, in case you use DATE.

If you want to convert those 0s, you need to use a TIMESTAMP and FF9:

SELECT TO_TIMESTAMP( '31-AUG-09 11.54.00.000000000 AM',
                     'DD-MON-YY HH.MI.SS.FF9 AM' )
FROM dual

You have another problem though: Use MI instead of MM, since MM is month and can not be used twice.

Answer 2

You can use FF9 to represent the fractional seconds part.