ORACLE 日期相差多少天、小时、分钟、秒

系统管理员 2022-01-27 00:35 528阅读 0赞

SELECT (CASE
             WHEN EXTRACT(DAY FROM(TO_DATE('2019-05-24 23:59:59',
                                       'YYYY-MM-DD HH24:MI:SS') -
                               TO_DATE('2019-05-23 00:00:00',
                                       'YYYY-MM-DD HH24:MI:SS')) DAY TO SECOND) > 0 THEN
              (SELECT EXTRACT(DAY FROM(TO_DATE('2019-05-24 23:59:59',
                                           'YYYY-MM-DD HH24:MI:SS') -
                                   TO_DATE('2019-05-23 00:00:00',
                                           'YYYY-MM-DD HH24:MI:SS')) DAY TO
                              SECOND) || '天' ||
                      EXTRACT(HOUR FROM(TO_DATE('2019-05-24 23:59:59',
                                           'YYYY-MM-DD HH24:MI:SS') -
                                   TO_DATE('2019-05-23 00:00:00',
                                           'YYYY-MM-DD HH24:MI:SS')) DAY TO
                              SECOND) || '小时' ||
                      EXTRACT(MINUTE FROM(TO_DATE('2019-05-24 23:59:59',
                                           'YYYY-MM-DD HH24:MI:SS') -
                                   TO_DATE('2019-05-23 00:00:00',
                                           'YYYY-MM-DD HH24:MI:SS')) DAY TO
                              SECOND) || '分钟'
                 FROM DUAL)
             WHEN EXTRACT(HOUR FROM(TO_DATE('2019-05-24 23:59:59',
                                       'YYYY-MM-DD HH24:MI:SS') -
                               TO_DATE('2019-05-23 00:00:00',
                                       'YYYY-MM-DD HH24:MI:SS')) DAY TO SECOND) > 0 THEN
              (SELECT EXTRACT(HOUR FROM(TO_DATE('2019-05-24 23:59:59',
                                           'YYYY-MM-DD HH24:MI:SS') -
                                   TO_DATE('2019-05-23 00:00:00',
                                           'YYYY-MM-DD HH24:MI:SS')) DAY TO
                              SECOND) || '小时' ||
                      EXTRACT(MINUTE FROM(TO_DATE('2019-05-24 23:59:59',
                                           'YYYY-MM-DD HH24:MI:SS') -
                                   TO_DATE('2019-05-23 00:00:00',
                                           'YYYY-MM-DD HH24:MI:SS')) DAY TO
                              SECOND) || '分钟'
                 FROM DUAL)
             WHEN EXTRACT(MINUTE FROM(TO_DATE('2019-05-24 23:59:59',
                                       'YYYY-MM-DD HH24:MI:SS') -
                               TO_DATE('2019-05-23 00:00:00',
                                       'YYYY-MM-DD HH24:MI:SS')) DAY TO SECOND) > 0 THEN
              (SELECT EXTRACT(MINUTE FROM(TO_DATE('2019-05-24 23:59:59',
                                           'YYYY-MM-DD HH24:MI:SS') -
                                   TO_DATE('2019-05-23 00:00:00',
                                           'YYYY-MM-DD HH24:MI:SS')) DAY TO
                              SECOND) || '分钟'
                 FROM DUAL)
             ELSE
              EXTRACT(SECOND FROM(TO_DATE('2019-05-24 23:59:59',
                                   'YYYY-MM-DD HH24:MI:SS') -
                           TO_DATE('2019-05-23 00:00:00',
                                   'YYYY-MM-DD HH24:MI:SS')) DAY TO SECOND) || '秒'
           END)
      FROM DUAL

输出：

1天23小时59分钟

微信公众号：CodeD  
![微信公众号][20200514112419921.jpg]

[20200514112419921.jpg]: /images/20220127/3ba4020414c24c78ab9e1b5945fee648.png