Oracle date format issue

I'm trying to export/import data in .csv format using SQLDeveloper. The so开发者_Python百科urce 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?

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.

You can use FF9 to represent the fractional seconds part.