Search code examples
sqloracle-databaseintervals

format interval with to_char

Following SQL command

select TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))) from table1

produces a result of the format: +000000000 00:03:01.954000.

Is it possible to enter a special format in the to_char function in order to get a result of format: +00 00:00:00.000?

Solution

  • I realize it's not clever at all, nor is it the special format string you're looking for, but this answer does work, given that the output is fixed length:

    SELECT    SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 1, 1)
       || SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 9, 2)
       || ' '
       || SUBSTR(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00'))), 12, 12)
  FROM table1;

    It also just truncs the fractional seconds instead of rounding, but I assume from your example they're all just zeros anyway.

    This is an even greater embarrassment, but I couldn't resist:

    SELECT SUBSTR(REPLACE(TO_CHAR(NVL(arg1 - arg2, TO_DSINTERVAL('0 00:00:00')))
                     , '0000000', '')
             , 1, 16)
  FROM table1;