SQL Query Error: date format picture ends before converting entire input into string

Question

I'm getting an Error when I run the query below:

 to_date('30-APR-19 09.53.35.000000 AM', 'DD-Mon-yy hh24.mi.ss')

Date format picture ends before converting entire input into string

Can I get an assistance please

Answer 1

You can directly use to_date function and miliseconds can be ignored using # as following:

to_date('30-APR-19 09.53.35.000000 AM', 'DD-MON-YY HH.MI.SS.###### AM')

Number of # is equal to number of 0s after dot(.)

db<>fiddle demo

Cheers!!

Answer 2

The major problem you've got is that your date-and-time string can't be parsed using TO_DATE - you'll need to use TO_TIMESTAMP. The issue is that TO_DATE doesn't recognize the FFn format specifier, which is used to process fractional seconds. This makes sense because DATE values are only accurate to the second. So you'll need to use

TO_TIMESTAMP('30-APR-19 09.53.35.000000 AM', 'DD-MON-YY HH.MI.SS.FF6 AM')

Which will return a TIMESTAMP value. If you really need this to be a DATE rather than a TIMESTAMP you can cast the value to DATE by using

CAST(TO_TIMESTAMP('30-APR-19 09.53.35.000000 AM', 'DD-MON-YY HH.MI.SS.FF6 AM') AS DATE)

dbfiddle here