8 Replies Latest reply: Dec 14, 2012 11:29 PM by benz RSS

    concatinating the time with date..

    benz
      Hi,
      I have column in a table which store the time from 00:00 to 23:00 hr with different interval for each item.
      I have to display the time from that column with current date.
      eg:
      Table A:
      expected_time
      22:40
      23:00
      00:30
      01:20
      04:10
      04:40

      Table B:
      Item expected_dep
      xyz
      abc
      I want query to insert into expected_dep in table B as sysdate + min value from the expected_time where to_char(sysdate,'hh24:mi)>  current time
      Suppose sysdate is 2012-12-08 and current sys time is 23:25 then the expected_dep should be 2012-12-09 00:30.
      Can anybody help to solve this query.
      Thanks in advance..
        • 1. Re: concatinating the time with date..
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: concatinating the time with date..
            jeneesh
            You havent provided the required information..

            And your explanation and the expected output is not matching

            Hopefully the below will help you
            define currentdate="to_date('2012-12-08 23:25','yyyy-mm-dd hh24:mi')"
            --"I have used currentdate variable to test your data"
            --"you can use sysdate instead of that"
            update b
            set expected_dep = 
                  (
                    select trunc(&currentdate)+1+
                           (
                            min(to_date(a.expected_time,'hh24:mi'))-
                            trunc(min(to_date(a.expected_time,'hh24:mi')))
                           )
                    from a
                    where to_date(a.expected_time,'hh24:mi') < 
                           to_date(to_char(&currentdate,'hh24:mi'),'hh24:mi')
                  );
            
            
            select *
            from b;
            
            ITEM       EXPECTED_DEP         
            ---------- ----------------------
            xyz        09-dec-2012 00:30:00   
            abc        09-dec-2012 00:30:00
            If this is not you want, please provide sample data (CREATE tABLE and INSERT statements) and expected output.

            Use {noformat}
            {noformat} tags to format your data and sqls..
            
            Read the FAQ as already mentioned
            
            Edited by: jeneesh on Dec 9, 2012 11:09 AM
            Update can be simplified
            update b
            set expected_dep =
            (
            select to_date(
            to_char(&currentdate+1,'ddmmyyyy')||
            min(a.expected_time)
            ,'ddmmyyyyhh24:mi'
            )
            from a
            where a.expected_time <
            to_char(&currentdate,'hh24:mi')
            );
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
            • 3. Re: concatinating the time with date..
              benz
              Thanks Jeneesh, With your above query I am getting the following error.
              SQL> update b
                2  set expected_dep = 
                3        (
                4          select  to_date(
                5                   to_char(&currentdate+1,'ddmmyyyy')||
                6                   min(a.expected_time)
                7                         ,'ddmmyyyyhh24:mi'
                8                          )
                9          from a
               10          where a.expected_time < 
               11                to_char(&currentdate,'hh24:mi')
               12        );
              Enter value for currentdate: 
              old   5:                  to_char(&currentdate+1,'ddmmyyyy')||
              new   5:                  to_char(+1,'ddmmyyyy')||
              Enter value for currentdate: SYSDATE
              old  11:               to_char(&currentdate,'hh24:mi')
              new  11:               to_char(SYSDATE,'hh24:mi')
                            to_char(SYSDATE,'hh24:mi')
                            *
              ERROR at line 11:
              ORA-01843: not a valid month
              Anyhow I am forwarding my question in detail with correct format below:
              create table A
              (expected_time date);
              /
              insert into a values (to_date('01:00','hh24:mi'));
              insert into a values (to_date('02:00','hh24:mi'));
              insert into a values (to_date('06:00','hh24:mi'));
              insert into a values (to_date('22:00','hh24:mi'));
              insert into a values (to_date('23:00','hh24:mi'));
              insert into a values (to_date('00:00','hh24:mi'));
              commit;
              select to_char(a.expected_time,'hh24:mi') from A;
              TO_CH
              -----
              01:00
              02:00
              06:00
              22:00
              23:00
              00:00
              
               create table b
               (Item  varchar2(100),
               expected_dep date);
              
              insert into b values ('xyz',null);
              insert into b values ('abc',null);
              insert into b values ('def',null);
              insert into b values ('ghi',null);
              Commit;
              
              select * from B;
              ITEM          EXPECTED_DEP
              xyz
              abc
              def
              ghi
              Actually we need when we update the expected_dep of table B it's should pick the time from expected_time column from table B.
              Suppose
              *Scenario 1:*
              IF current time(sysdate) is 2012-12-01 10:00 then the expected_dep date should be 2012-12-01 22:00.
              Expected output:
              select * from B;
              ITEM          EXPECTED_DEP
              xyz           2012-12-01 22:00
              abc           2012-12-01 22:00
              def           2012-12-01 22:00
              ghi           2012-12-01 22:00
              *Scenario 2:* 
              IF current time(sysdate) is 2012-12-01 23:10 then the expected_dep date should be 2012-12-02 00:00.
              Expected output:
              ITEM          EXPECTED_DEP
              xyz           2012-12-02 00:00
              abc           2012-12-02 00:00
              def           2012-12-02 00:00
              ghi           2012-12-02 00:00
              Thanks in advance
              • 4. Re: concatinating the time with date..
                Solomon Yakobson
                Use:
                update  b
                  set expected_dep = (
                                      select  nvl(trunc(sysdate) + min(expected_time - trunc(expected_time)),trunc(sysdate) + 1)
                                        from  a
                                        where to_char(expected_time,'hh24:mi') >= to_char(sysdate,'hh24:mi')
                                     )
                /
                And to mimic "current time(sysdate) is 2012-12-01 10:00 & 2012-12-01 23:10":
                SQL> update  b
                  2    set expected_dep = (
                  3                        select  nvl(trunc(to_date('2012-12-01 10:00','yyyy-mm-dd hh24:mi')) + min(expected_time - trunc(expected_time)),trunc(to_date('2012-12-01 10:00','yyyy-mm-dd hh24:mi')) + 1)
                  4                          from  a
                  5                          where to_char(expected_time,'hh24:mi') >= to_char(to_date('2012-12-01 10:00','yyyy-mm-dd hh24:mi'),'hh24:mi')
                  6                       )
                  7  /
                
                4 rows updated.
                
                SQL> select  *
                  2    from  b
                  3  /
                
                ITEM       EXPECTED_DEP
                ---------- -------------------
                xyz        12/01/2012 22:00:00
                abc        12/01/2012 22:00:00
                def        12/01/2012 22:00:00
                ghi        12/01/2012 22:00:00
                
                SQL> update  b
                  2    set expected_dep = (
                  3                        select  nvl(trunc(to_date('2012-12-01 10:00','yyyy-mm-dd hh24:mi')) + min(expected_time - trunc(expected_time)),trunc(to_date('2012-12-01 10:00','yyyy-mm-dd hh24:mi')) + 1)
                  4                          from  a
                  5                          where to_char(expected_time,'hh24:mi') >= to_char(to_date('2012-12-01 23:10','yyyy-mm-dd hh24:mi'),'hh24:mi')
                  6                       )
                  7  /
                
                4 rows updated.
                
                SQL> select  *
                  2    from  b
                  3  /
                
                ITEM       EXPECTED_DEP
                ---------- -------------------
                xyz        12/02/2012 00:00:00
                abc        12/02/2012 00:00:00
                def        12/02/2012 00:00:00
                ghi        12/02/2012 00:00:00
                
                SQL>
                SY.
                • 5. Re: concatinating the time with date..
                  jeneesh
                  benz wrote:
                  Thanks Jeneesh, With your above query I am getting the following error.
                  You have to replace &currentdate with sysdate in the query.....
                  update b
                  set expected_dep = 
                        (
                          select  to_date(
                                   to_char(sysdate+1,'ddmmyyyy')||
                                   min(a.expected_time)
                                         ,'ddmmyyyyhh24:mi'
                                          )
                          from a
                          where a.expected_time < 
                                to_char(sysdate,'hh24:mi')
                        );
                  • 6. Re: concatinating the time with date..
                    benz
                    Hi Solomon,
                    Thanks for the reply..
                    It's work perfect when we put the value but when we put the sysdate instead of value it's not coming correct.
                    The below are the output result you can see the time is ok but the date is not correct.
                    SQL> select to_char(sysdate,'dd-mm-yyyy hh24:mi') from dual;
                    
                    TO_CHAR(SYSDATE,
                    ----------------
                    10-12-2012 02:01
                    
                    SQL> select * from a;
                    
                    EXPECTED_TIME
                    ----------------
                    01-12-2012 01:00
                    01-12-2012 02:00
                    01-12-2012 06:00
                    01-12-2012 22:00
                    01-12-2012 23:00
                    01-12-2012 00:00
                    
                    6 rows selected.
                    
                    SQL>  select  nvl(trunc(sysdate) + min(expected_time - trunc(expected_time)),trunc(sysdate) + 1)
                      2                           from  a
                      3                           where to_char(expected_time,'hh24:mi') >= to_char(sysdate,'hh24:mi');
                    
                    NVL(TRUNC(SYSDAT
                    ----------------
                    10-12-2012 06:00
                    • 7. Re: concatinating the time with date..
                      Solomon Yakobson
                      benz wrote:
                      The below are the output result you can see the time is ok but the date is not correct.
                      You need to be more specific. What result do you expect? The way I understood your reqs is we need to find next closest to sysdate time in table a. So if current time is 02:01 next closest time in table a is 6:00. If you want to get time closest to current time either direction:
                      update  b
                        set expected_dep = (
                                            with t1 as (
                                                         select  expected_time - trunc(expected_time) d
                                                           from  a
                                                        union all
                                                         select  1
                                                           from  dual
                                                       ),
                                                 t2 as (
                                                        select  trunc(sysdate) + d expected_dep,
                                                                row_number() over(order by abs(d - (sysdate - trunc(sysdate)))) r
                                                          from  t1
                                                       )
                                            select  expected_dep
                                              from  t2
                                              where r = 1
                                           )
                      /
                      And to mimic 2012-12-10 02:01:
                      update  b
                        set expected_dep = (
                                            with t1 as (
                                                         select  expected_time - trunc(expected_time) d
                                                           from  a
                                                        union all
                                                         select  1
                                                           from  dual
                                                       ),
                                                 t2 as (
                                                        select  trunc(to_date('2012-12-10 02:01','yyyy-mm-dd hh24:mi')) + d expected_dep,
                                                                row_number() over(order by abs(d - (to_date('2012-12-10 02:01','yyyy-mm-dd hh24:mi') -
                                                                trunc(to_date('2012-12-10 02:01','yyyy-mm-dd hh24:mi'))))) r
                                                          from  t1
                                                       )
                                            select  expected_dep
                                              from  t2
                                              where r = 1
                                           )
                      /
                      
                      4 rows updated.
                      
                      SQL> select  *
                        2    from  b
                        3  /
                      
                      ITEM       EXPECTED_DEP
                      ---------- -------------------
                      xyz        12/10/2012 02:00:00
                      abc        12/10/2012 02:00:00
                      def        12/10/2012 02:00:00
                      ghi        12/10/2012 02:00:00
                      
                      SQL> 
                      SY.
                      • 8. Re: concatinating the time with date..
                        benz
                        It's perfect work for me.

                        Thanks everyone for the support.