how to truncate yyyy/mm/dd hh:mm:ss.SSS to mm/dd/yyyy in oracle

Question

When I ran a query to get date it is retrieved in this format 'yyyy/mm/dd hh:mm:ss.SSS' but I need to convert it to mm/dd/yyyy.

I'm using this query for conversion

select 
    to_char(
        add_months (
            to_date(
                to_char(
                    trunc(
                        TO_DATE('2016/01/01 00:00:00.0', 'YYYY/MM/DD HH24:MI:SS.SSS')
                    ), 'MM/DD/YYYY' -- to char
            ),'MM/DD/YYYY' -- to date
        ), -2*1  -- add months
    ), 'MM/DD/YYYY' -- to char
) START_DATE,

to_char(
    add_months (
        to_date(
            to_char(
                trunc(
                    TO_DATE('2017/01/01 00:00:00.0', 'YYYY/MM/DD HH24:MI:SS.SSS')
                ), 'MM/DD/YYYY' -- to char
            ), 'MM/DD/YYYY'  -- to date
         ), 3 -- add months
    ), 'MM/DD/YYYY' -- to char
) END_DATE

from dual;

Output is

ORA-01810: format code appears twice
01810. 00000 -  "format code appears twice"

Why: to_char( add_months ( to_date ( to_char ( trunc ( to_date ( "string")))))) - use `to_char( trunc ( add_months ( to_date ( "string"))))` — Patrick Artner, Dec 13 '17 at 07:50
The problem here is you are using SS multiple times. instead of YYYY/MM/DD HH24:MI:SS.SSS you shoud use YYYY/MM/DD HH24:MI:SS — Surajit Kundu, Dec 13 '17 at 07:54

arunb2w · Accepted Answer · 2017-12-13T08:02:34.040

The problem is in the conversion of to_date itself. The below conversion itself is throwing the error you mentioned

select 
TO_DATE('2017/01/01 00:00:00.0', 'YYYY/MM/DD HH24:MI:SS.SSS') END_DATE
from dual;

You need to use it like below if you want to convert the string with timestamp to timestamp

select TO_TIMESTAMP('2017/01/01 00:00:00.0', 'YYYY/MM/DD HH24:MI:SS.FF') from dual

This will simply satisfy your need rather than making so many conversions.

select to_char(
   add_months(
     TO_TIMESTAMP('2017/01/01 00:00:00.0', 'YYYY/MM/DDHH24:MI:SS.FF'),
   -2),
'mm/dd/yyyy') from dual

APC · Answer 2 · 2017-12-13T08:00:30.610

ORA-01810: format code appears twice

That's because of SS.SSS. SSS is not a valid date format. You are trying to handle fractional seconds but:

the correct format mask for that is FF
DATE doesn't support fractional seconds, only TIMESTAMP

Really date format is a display issue and should be handled by the client's NLS settings. But if you really must do it in SQL this is all you need:

select 
    to_char(DATE '2015-11-01', 'MM/DD/YYYY') START_DATE
    , to_char(DATE '2017-04-01', 'MM/DD/YYYY') END_DATE
from dual;

You don't need trunc() because the date literals are already set to midnight. You don't need add_months() because you can just change the value of the date literal. You don't need to cast the date to a string back to a date because you just don't.

how to truncate yyyy/mm/dd hh:mm:ss.SSS to mm/dd/yyyy in oracle

2 Answers2