0

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"

James Z
  • 12,209
  • 10
  • 24
  • 44
fatherazrael
  • 5,511
  • 16
  • 71
  • 155
  • DATE data type doesn't have fractional seconds and timezone, you need to use TIMESTAMP. To keep it simple you could use the ANSI literal which uses a fixed format. See documentation for datetime literals https://docs.oracle.com/database/121/SQLRF/sql_elements003.htm#SQLRF51062 – Lalit Kumar B Apr 14 '20 at 08:04
  • @LalitKumarB: I do not need timestamp actually. I just want to either ignore .0 or omit the same. The type will be date for all – fatherazrael Apr 14 '20 at 08:08
  • If you want to stick to TO_DATE, then get rid of `.0` from the input and in the format mask remove `.TZR`. DATE data type can only store date and time portion till seconds only. It could be simply `DATE '2014-04-01'` – Lalit Kumar B Apr 14 '20 at 08:10
  • 1
    It seems you have got a `java.sql.Timestamp` and applied its `toString` method. The `Timestamp` class is poorly designed and long outdated, so first see if instead you can get an `Instant`, `OffsetDateTime` or `LocalDateTime`. Next send one of those into your database rather than a string so you don’t need any conversion on the SQL side. – Ole V.V. Apr 14 '20 at 08:12
  • @LalitKumarB: What is .0 at end? and any oracle equivalent for same? – fatherazrael Apr 14 '20 at 08:13
  • 1
    As I already said, .0 is the fractional seconds part. Read the documentation link I provided above. DATE cannot have anything beyond seconds, TIMESTAMP can store fractional seconds and timezone values. – Lalit Kumar B Apr 14 '20 at 08:14
  • Ok So i do this :( -> select TO_DATE (REPLACE('2020-04-01 01:00:00.0','.0',''),'YYYY-MM-DD HH24:MI:SS') from dual – fatherazrael Apr 14 '20 at 08:22
  • `REPLACE('2020-04-01 01:00:00.0','.0','')` can be simplified to `'2020-04-01 01:00:00'` –  Apr 14 '20 at 08:28
  • 2
    @fatherazrael That would work. But it would be better if you get it as DATE or TIMESTAMP from the Java application itself than manipulating the literal in SQL. – Lalit Kumar B Apr 14 '20 at 08:28
  • 2
    "*I got this date from Java and is date type.*" - you should retrieve and store DATE values through `java.time.LocalDateTime` (or at least `java.sql.Timestamp`) **not** as strings. –  Apr 14 '20 at 08:30

2 Answers2

2

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0

Such format can be easily converted by type info before

SELECT timestamp'2020-04-01 01:02:03.4' from DUAL

and to_timestamp can also be used in select stmt.

Tomáš Řezníček
  • 1
  • 1
  • 1