It's Five O'Clock Somewhere...

Is it Friday Five O'Clock Yet???

Here's a little way you can do a countdown to Friday Five O'Clock using a simple little Oracle PL/SQL function and query.

Enjoy!

Until next time...Rich

CREATE OR REPLACE FUNCTION fx_elapsed_string(
    p_start                 DATE,
    p_end                   DATE)
RETURN VARCHAR2 IS
    lv_big                  DATE;
    lv_small                DATE;
    lv_string               VARCHAR2(120);
BEGIN
    IF      p_start > p_end
    THEN    lv_big := p_start;
            lv_small := p_end;
    ELSE    lv_big := p_end;
            lv_small := p_start;
    END IF;

    SELECT (extract(day from ((lv_big - lv_small) day to second))*24) +
           extract(hour from ((lv_big - lv_small) day to second))||':'||
           substr('0'||
           extract(minute from ((lv_big - lv_small) day to second)), -2)
            ||':'||
           substr('0'||
          extract(second from ((sysdate - lv_small) day to second)), -2)
    INTO    lv_string
    FROM DUAL;

    RETURN lv_string;

EXCEPTION 
WHEN others THEN RAISE;
END;
/
show errors

SQL> SELECT fx_elapsed_string(sysdate,  
       trunc(NEXT_DAY(SYSDATE, 'FRI')) + 17/24) "HH:MM:SS until Friday 5pm"
     FROM DUAL;

HH:MM:SS until Friday 5pm
----------------------------------
34:14:00

Note: This assumes your database server is in your same timezone!!!

Comments

Popular Posts