Search code examples
sqloraclesqldatetime

Formatting date to 2017-06-21T12:33:47.879 format

In DB I have TIMESTAMP datatype like 17/06/21 12:33:47,879000000 and I would like to select in format 2017-06-21T12:33:47.879

Edit: I tried to_char(MODIFIED_TIME,'YYYY-MM-DD HH:MI:SS:FF') but getting

2010-11-12 11:47:50:294000. How can I add this 'T' and cut these '0' at the end of milliseconds?

Solution

  • You can use such a formatting model :

    SELECT TO_CHAR( systimestamp ,'yyyy-mm-dd"T"HH24:MI:SS.FF') AS ts
  FROM dual;

 TS
 -----------------------
 2020-08-26T09:51:25.600016

    or

    SELECT TO_CHAR( timestamp'2017-06-21 12:33:47.879000000', 'yyyy-mm-dd"T"HH24:MI:SS.FF' )  
    AS ts
  FROM dual;

 TS
 -----------------------
 2017-06-21T12:33:47.879000000

    for a spesific date and time.

    Alternatively use

    SELECT TO_CHAR( timestamp'2017-06-21 12:33:47.879000000', 'yyyy-mm-dd"T"HH24:MI:SS.FF3' ) AS ts,
       CAST( timestamp'2017-06-21 12:33:47.879000000' AS TIMESTAMP(3) ) As ts2
  FROM dual;

 TS                        TS2
 -----------------------   -----------------------
 2017-06-21T12:33:47.879   2017-06-21T12:33:47.879

    to get precision set to 3 for milliseconds portion.