Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    46

    Unanswered: I want to create a variable that will show the 106 as 1:46

    Hi,

    I want to create a variable that will show the 106 as 1:46 ( one hour and forty six minutes)

    Of course after dividing by 60 I get 1.77 and tried to reformat as HH:mm with no luck

    Regard's
    Prasad

  2. #2
    Join Date
    Feb 2009
    Posts
    62
    You can't just create a variable that will display like that.

    You could create a function that would take the value 106 and return the string '1:46' - you'd use the MOD and FLOOR functions to do the internal calculation.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can do this:

    Code:
    with sample_data as
    ( select rownum as mins
      from all_objects
      where rownum <= 120
    )
    select mins
    ,      to_char(floor(mins/60),'FM00') || ':' || to_char(mod(mins,60),'FM00') formatted_mins
    from   sample_data
    where  mins between 106 and 120;
    
          MINS FORMATT
    ---------- -------
           106 01:46
           107 01:47
           108 01:48
           109 01:49
           110 01:50
           111 01:51
           112 01:52
           113 01:53
           114 01:54
           115 01:55
           116 01:56
           117 01:57
           118 01:58
           119 01:59
           120 02:00

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    The other option is to actually treat them like minutes. If your minutes go over 1440 then you need to handle that or use Tony's approach.
    Code:
    CREATE OR REPLACE FUNCTION format_minutes(
      p_min  NUMBER,
      p_fmt  VARCHAR2   DEFAULT 'HH24:MI'
    ) RETURN VARCHAR2 AS
      v_dt    DATE;
    BEGIN
      v_dt := trunc(SYSDATE) + (p_min/1440);
      RETURN to_char(v_dt, p_fmt);
    END;
    
    -- Usage
    
    with sample_data as
    ( select rownum as mins
      from all_objects
      where rownum <= 120
    )
    SELECT mins, format_minutes(mins) fm, format_minutes(mins, 'FMHH:FMMI AM') fm2
    FROM sample_data
    WHERE mod(mins, 10) = 1;
    
      
     MINS     FM     FM2      
     -------  -----  -------- 
     1        00:01  12:01 AM 
     11       00:11  12:11 AM 
     21       00:21  12:21 AM 
     31       00:31  12:31 AM 
     41       00:41  12:41 AM 
     51       00:51  12:51 AM 
     61       01:01  1:01 AM  
     71       01:11  1:11 AM  
     81       01:21  1:21 AM  
     91       01:31  1:31 AM  
     101      01:41  1:41 AM  
     111      01:51  1:51 AM
    Last edited by artacus72; 09-09-09 at 11:06.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •