Search code examples
javaoracledate-formattingto-datestr-to-date

What is oracle format for last .0 in TO_DATE ('2020-04-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.TZR')?

I got this date from Java and is date type.

So trying to format it in oracle 

select TO_DATE ('2020-04-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.TZR') from DUAL

not able to format last .0 in date. Tried various way

.TZR
  TZR

Not sure how to format that? If i remove .0 and .TZR then things work fine.

I am getting following error:

ORA-01820: format code cannot appear in date input format 01820. 00000 - "format code cannot appear in date input format"

Solution

  • The .0 is fractional seconds, not a timezone offset; but the date data type doesn't allow either anyway.

    If your string always has .0 then you could treat that as fixed characters to ignore it:

    select TO_DATE ('2020-04-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS".0"') from DUAL;

TO_DATE('2020-04-01
-------------------
2020-04-01 00:00:00

    If it might be non-zero or just if you prefer it can convert to a timestamp and then cast that to a date:

    select CAST( TO_TIMESTAMP ('2020-04-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.FF') AS DATE) from DUAL;

CAST(TO_TIMESTAMP('
-------------------
2020-04-01 00:00:00

    The FF format model element represents fractional seconds, and as the documentation says, that is "Valid in timestamp and interval formats, but not in DATE formats".

    It isn't entirely cleat what getting the value from Java means, but if you're making a JDBC call and passing the parameter as a string you should step back and use the proper JDBC data type instead. Also note @Ole's comment about using a modern Java datatype if you can.