1 Reply Latest reply on Jun 17, 2019 5:52 AM by RogerT

    TIMES segmentation

    Jelly

              WITH A AS

          (

          SELECT TO_DATE('2019-06-16 0300','yyyy-MM-DD HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 0330','yyyy-MM-DD HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 0400','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 1600','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 1630','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 2300','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-16 2330','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-17 0000','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-17 0030','yyyy-MM-DD  HH24:MI') YMD from dual

          UNION ALL

          SELECT TO_DATE('2019-06-17 0100','yyyy-MM-DD  HH24:MI') YMD from dual

          )

       

       

          SELECT * FROM A

         

       

      I WISH RESULT SELECT

       

      STARTDATE       STARTTIME     ENDDATE         ENDTIME

      2019-06-16             0300                2019-06-16          0400

      2019-06-16             1600                 2019-06-16         1630

      2019-06-16             2300                  2019-16-17         0000

      2019-06-17             0000                  2019-16-17         0100

        • 1. Re: TIMES
          RogerT

          Let's start with:

           

          there is no reason to have the time in a separate column as a date column includes time to_date('16.06.2019 03:00:00','dd.mm.yyyy hh24:mi:ss')

           

          and then it would be nice to have some hints regarding the logic you want to implement like ... why is 03:30 and 23:30 not part of your result? My crystal ball is out of order so no chance to give you advice.

           

          hth