使用 to_char 格式化间隔

2021-12-05 00:00:00 sql intervals oracle

遵循 SQL 命令

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

产生以下格式的结果:+000000000 00:03:01.954000.

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

是否可以在to_char函数中输入特殊格式才能得到格式的结果:+00 00:00:00.000?

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?

推荐答案

我意识到它根本不聪明，也不是您正在寻找的特殊格式字符串，但是鉴于输出是固定的，这个答案确实有效长度:

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;

相关文章