1 2 Previous Next 25 Replies Latest reply: May 29, 2012 12:55 PM by GMoney RSS

    Convert a number representing "Working Day" to hours

    GMoney
      I have a query that is pulling in a column <Completion_Days> that list working days as a value that appears to range between 0 and up to possibly 10 or higher.
      What I need to do is convert that numerial to a time format of HH:MN:SS.

      There are a other columns <Completion_Hours> and <Completion_Minutes> that I also want to convert to a time format as well. Here is a sample of my data:

      SEQUENCE     DAYS     COMPLETION_HOURS     COMPLETION_MINUTES
      1     0     0     15
      2     0     0     15
      3     1     0     0
      4     0     0     15
      5     0     0     15
      6     1     0     0
      7     0     0     15
      8     0     0     15
      9     0     4     0
      10     0     0     15
      11     0     4     0
      12     0     4     0
        • 1. Re: Convert a number representing "Working Day" to hours
          GMoney
          Here is a better representation of the data:

          SEQUENCE     COMPLETION_DAYS     COMPLETION_HOURS     COMPLETION_MINUTES
          1          0               0               15
          2          0               0               15
          3          1               0               0
          4          0               0               15
          5          0               0               15
          6          1               0               0
          7          0               0               15
          8          0               0               15
          9          0               4               0
          10          0               0               15
          11          0               4               0
          12          0               4               0
          • 3. Re: Convert a number representing "Working Day" to hours
            Frank Kulash
            Hi,
            GMoney wrote:
            Here is a better representation of the data:
            It looks the same to me.

            You may have noticed that this site normally doesn't display multiple spaces in a row.
            Whenever you post formatted text (such as query results) on this site, type these 6 characters:

            \
            (small letters only, inside curly brackets) before and after each section of formatted text, to preserve spacing.
            
            When posting sample data, post CREATE TABLE and INSERT statements.
            Don't forget to post the results you want from that data, also, and an explanation of how you might get those results from that data.
            See the forum FAQ {message:id=9360002}
            
            INTERVAL_DAY_TO_SECONDS display days, hours, minutes and seconds nicely.  It might display more than you want to see, but if you convert it to a VARCHAR2, then you can use functions like LTRIM, SUBSTR and REGEXP_REPLACE to get exactly what you want.  Use NUMTODSINTERVAL to convert NUMBERS to INTERVAL DAY TO SECONDS.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
            • 4. Re: Convert a number representing "Working Day" to hours
              Solomon Yakobson
              GMoney wrote:
              What I need to do is convert that numerial to a time format of HH:MN:SS.
              Something like:
              (DAYS * 24 + COMPLETION_HOURS) || ':' || COMPLETION_MINUTES || ':00'
              SY.
              • 5. Re: Convert a number representing "Working Day" to hours
                GMoney
                Thanks for the responses.

                Here is the short and sweet answer:

                TRUNC(SYSDATE) + <COMPLETION_DAYS> - TRUNC(SYSDATE)) * 28800 AS WRK_DAYS
                • 6. Re: Convert a number representing "Working Day" to hours
                  Solomon Yakobson
                  GMoney wrote:

                  Here is the short and sweet answer:
                  Neither short nor sweet. Last time I touched arithmetic A + B - A still was B. So:
                  TRUNC(SYSDATE) + <COMPLETION_DAYS> - TRUNC(SYSDATE)
                  Is nothing but COMPLETION_DAYS. 28800 is 3600 * 8. So, I am guessing by "working day" you meant 8 hour days. Anyway, your "short nor sweet" answer is nothing but:
                  <COMPLETION_DAYS> * 28800
                  And represenst number of <COMPLETION_DAYS> work SECONDS, not HOURS as you asked on your original question.

                  SY.
                  • 7. Re: Convert a number representing "Working Day" to hours
                    GMoney
                    As Solomon stated I got ahead of myself, so reopening the question.
                    • 8. Re: Convert a number representing "Working Day" to hours
                      AlbertoFaenza
                      GMoney wrote:
                      As Solomon stated I got ahead of myself, so reopening the question.
                      Well, it should be nice to understand exactly what you mean with your original question:
                      What I need to do is convert that numerial to a time format of HH:MN:SS.

                      There are many of us who are probably able to answer your question. But you should try to explain a bit better what you expect.

                      Are you trying to sum to current date days + hrs + min?

                      So in a case where Completion_Days = 1, Completion_Hours=0 and Completion_Minutes=0 what do you expect to have as time format HH:MI:SS?

                      Try to give an example of what you expect as output please.

                      Regards.
                      Al
                      • 9. Re: Convert a number representing "Working Day" to hours
                        GMoney
                        Let me try this again. The simplistic short sweet did not work :( .
                        I have a query that is pulling in 3 columns from the Task table. I believe the days are considered to 8 hours (but not 100% sure yet, waiting for confirmation).
                        What I need to do is combine the numerial values in these 3 columns into one column with a format of HH:MN:SS.

                        I am using this now but of course it only give me hours or decimals of hours (ie: 8, 0.25 etc.).
                        (nvl(T.COMPLETION_DAYS * 8,0)) + (nvl(T.COMPLETION_HOURS,0))  + (nvl(T.COMPLETION_MINUTES/60,0))as Plan_Hrs,
                        I found this code that Solomon had posted back in Mar 2010, that I think would work, but I have not got the formating/syntax correct. My columns are not dates
                        but instead NUMBER(2).

                        trunc((cast(rls_break.break_d_t as date) - cast(conc_test.cyl_cast_time as date)) *24) || to_char(trunc(sysdate) + abs(cast(rls_break.break_d_t as date) - cast(conc_test.cyl_cast_time as date)),':MI:SS')
                        COMPLETION_DAYS          COMPLETION_HOURS                   COMPLETION_MINUTES
                             0               0               15
                             0               0               15
                             1               0               0
                             0               0               15
                             0               0               15
                             1               0               0
                             0               0               15
                             0               0               15
                             0               4               0
                             0               0               15
                             0               4               0
                             0               4               0
                             1               0               0
                             1               0               0
                             0               0               15
                             0               0               15
                             2               0               0
                             1               0               0
                             0               0               15
                             0               0               15
                             0               0               15
                             5               0               0
                             1               0               0
                             2               0               0
                        Guidance please.

                        Thanks for looking.

                        G

                        Edited by: GMoney on May 24, 2012 7:52 AM
                        • 10. Re: Convert a number representing "Working Day" to hours
                          GMoney
                          Are you trying to sum to current date days + hrs + min?

                          Yes sum to current date days + hrs + min + ss as well even though there will not likely be anything other than :00.
                          (I will be using this result column for another purpose later, against a column that does contain ss.)

                          So in a case where Completion_Days = 1, Completion_Hours=0 and Completion_Minutes=0 what do you expect to have as time format HH:MI:SS?

                          So for example Completion_Days = 2, Completion_Hours=4 and Completion_Minutes=15

                          The output would then be like: 20:15:00 (if 1 day = 8 hours) Or 52:15:00 (if 1 day = 24 hours).
                          As I mentioned in my revised post I have not received confirmation on what the Completion_Days really represent yet so I would like to know how to
                          use as either 8 hrs or 24 hrs.

                          I appreciate your time,

                          G
                          • 11. Re: Convert a number representing "Working Day" to hours
                            indra budiantho
                            /* Formatted on 2012/05/24 22:33 (Formatter Plus v4.8.8) */
                            WITH t AS
                                 (SELECT 2 dd, 4 hh, 15 mn
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0 dd, 0 hh, 15 mn
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 1, 0, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 1, 0, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 4, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 4, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 4, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 1, 0, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 1, 0, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 2, 0, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 1, 0, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 0, 0, 15
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 5, 0, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 1, 0, 0
                                    FROM DUAL
                                  UNION ALL
                                  SELECT 2, 0, 0
                                    FROM DUAL)
                            SELECT    TO_CHAR ((8 * dd) + hh + FLOOR (mn / 60))
                                   || ':'
                                   || MOD (mn, 60)
                                   || ': 00' myday
                              FROM t;
                            outputs:

                            MYDAY

                            20:15: 00
                            0:15: 00
                            0:15: 00
                            8:0: 00
                            0:15: 00
                            0:15: 00
                            8:0: 00
                            0:15: 00
                            0:15: 00
                            4:0: 00
                            0:15: 00
                            4:0: 00
                            4:0: 00
                            8:0: 00
                            8:0: 00
                            0:15: 00
                            0:15: 00
                            16:0: 00
                            8:0: 00
                            0:15: 00
                            0:15: 00
                            0:15: 00
                            40:0: 00
                            8:0: 00
                            16:0: 00
                            • 12. Re: Convert a number representing "Working Day" to hours
                              GMoney
                              Result looks good, but method is very long. Isn't there a shorter method?

                              Thanks for looking.
                              • 13. Re: Convert a number representing "Working Day" to hours
                                indra budiantho
                                just look this:
                                SELECT TO_CHAR ((8 * dd) + hh + FLOOR (mn / 60))
                                || ':'
                                || MOD (mn, 60)
                                || ': 00' myday
                                FROM t;


                                don't look the WITH CLAUSE....


                                It's the shortest path :)..i can afford
                                • 14. Re: Convert a number representing "Working Day" to hours
                                  Solomon Yakobson
                                  GMoney wrote:

                                  So for example Completion_Days = 2, Completion_Hours=4 and Completion_Minutes=15

                                  The output would then be like: 20:15:00 (if 1 day = 8 hours) Or 52:15:00 (if 1 day = 24 hours).
                                  Then use:
                                  (Completion_Days * 8 + Completion_Hours) || ':' || Completion_Minutes || ':00'
                                  For example:
                                  with sample_table as (
                                                        select  2 Completion_Days,
                                                                4 Completion_Hours,
                                                                15 Completion_Minutes
                                                          from  dual
                                                       )
                                  select  (Completion_Days * 8 + Completion_Hours) || ':' || Completion_Minutes || ':00' res
                                    from  sample_table
                                  /
                                  
                                  RES
                                  --------
                                  20:15:00
                                  
                                  SQL> 
                                  SY.
                                  1 2 Previous Next