oracle 把整数转换成固定格式的日期，谢谢
比如 39884是一个整数，怎么把它转换成：几天几小时几分钟几秒这样的格式.如：10天10小时10分10秒。谢谢大家

[解决办法]
楼上的，应该用TURNC+MOD来实现吧
[解决办法]
呃，这个怎么样？

SQL code

SELECT    TRUNC (139884 / (60 * 60 * 24), 0)       || '天'       || MOD (TRUNC (139884 / (60 * 60), 0), 24)       || '时'       || MOD (TRUNC (139884 / 60, 0), 60)       || '分'       || MOD (TRUNC (139884 / 1, 0), 60)       || '秒' AS TIME  FROM DUAL;
[解决办法]
SQL code
-- TRY IT ..SQL> SELECT 10*24*60*60 + 10*60*60 + 10*60 + 10 TOTAL_SECOND_DAY,  2         11*60*60 + 12*60 + 18 TOTAL_SECOND_HOUR,  3         8*60 + 6 TOTAL_SECOND_MINUTE,  4         26 TOTAL_SECONDS  5    FROM DUAL;TOTAL_SECOND_DAY TOTAL_SECOND_HOUR TOTAL_SECOND_MINUTE TOTAL_SECONDS---------------- ----------------- ------------------- -------------          900610             40338                 486            26SQL> SELECT CASE  2           WHEN NUM >= 1 AND NUM < 60 -- SECOND  3            THEN  4            NUM || ' sens'  5           WHEN NUM >= 60 AND NUM < 60 * 60 -- MINUTE  6            THEN  7            TRUNC(NUM / 60) || ' mins :' || (NUM - (60 * TRUNC(NUM / 60))) ||  8            ' sens'  9           WHEN NUM >= 60 * 60 AND NUM < 24 * 60 * 60 -- HOURS 10            THEN 11            TRUNC(NUM / (60 * 60)) || ' hours :' || 12            TRUNC((NUM - (60 * 60 * TRUNC(NUM / (60 * 60)))) / 60) || 13            ' mins :' || 14            (NUM - (60 * 60 * TRUNC(NUM / (60 * 60))) - 15             (60 * TRUNC((NUM - (60 * 60 * TRUNC(NUM / (60 * 60)))) / 60))) || 16            ' sens' 17           WHEN NUM >= 24 * 60 * 60 -- DAYS 18            THEN 19            TRUNC(NUM / (24 * 60 * 60)) || ' days :' || 20            TRUNC((NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60)))) / 21                  (60 * 60)) || ' hours :' || 22            TRUNC((NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60))) - 23                  (60 * 60 * 24                  TRUNC((NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60)))) / 25                          (60 * 60)))) / 60) || 'mins :' || 26            (NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60))) - 27             (60 * 60 * 28             TRUNC((NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60)))) / 29                    (60 * 60))) - 30             (60 * TRUNC((NUM - (24 * 60 * 60 * TRUNC(NUM / (24 * 60 * 60))) - 31                         (60 * 60 * TRUNC((NUM - (24 * 60 * 60 * 32                                           TRUNC(NUM / (24 * 60 * 60)))) / 33                                           (60 * 60)))) / 60))) || ' sens' 34         END DAY_TIME 35    FROM (SELECT 900610 NUM 36            FROM DUAL 37          UNION ALL 38          SELECT 40338 NUM 39            FROM DUAL 40          UNION ALL 41          SELECT 486 NUM 42            FROM DUAL 43          UNION ALL 44          SELECT 26 NUM FROM DUAL);DAY_TIME--------------------------------------------10 days :10 hours :10mins :10 sens11 hours :12 mins :18 sens8 mins :6 sens26 sens
[解决办法]
select to_timestamp(sysdate+39884/24/60/60)-to_timestamp(sysdate) from dual;