This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Mar 7, 2013 12:18 AM by AlbertoFaenza RSS

Add hours and minutes to date

Sajeeva Lakmal Newbie
Currently Being Moderated
Hi,
I need to write a function which has two input parameters.
Parameter1 => Date data type, value is in GMT.
Parameter1 => String value which indicates the time offset and it has the following format.

GMT(sign)HH24Mi
for examples:
GMT+400 , GMT-1400

I need add the offset (param2) to the date (param1) and returns as the output.

I wrote a simple code but can't get it to work. I would like to know if there is an another way to do it.
create or replace
Function         F_FUNCTION_NAME(dt date,timeZoneOffset   in   varchar2)
return date
As
    offset  varchar2(9);    
Begin
    Offset := (To_Char(Substr(Substr(Timezoneoffset,4),0,Length(Substr(Timezoneoffset,4))-2)||':'||Substr(Substr(Timezoneoffset,4),-2)));
    return Dt + interval Offset hour to minute;
End;
Thank You.
  • 1. Re: Add hours and minutes to date
    Sajeeva Lakmal Newbie
    Currently Being Moderated
    Is there any type in Oracle so that the value 'GMT-1400' can be identified as a time offset straight away?
  • 2. Re: Add hours and minutes to date
    KeithJamieson Expert
    Currently Being Moderated
    yes. Its called timestamp with timezone
  • 3. Re: Add hours and minutes to date
    Sajeeva Lakmal Newbie
    Currently Being Moderated
    Parameters will be passed as follows
    select F_FUNCTION_NAME(sysdate,'GMT+1400') from dual;
    Edited by: Sajeeva Lakmal on Mar 6, 2013 3:11 AM
  • 4. Re: Add hours and minutes to date
    Sajeeva Lakmal Newbie
    Currently Being Moderated
    Thanks,
    Can you give me an example of how to use it? In this case, The value 'GMT-0400' is a string value.
  • 5. Re: Add hours and minutes to date
    jeneesh Guru
    Currently Being Moderated
    Sajeeva Lakmal wrote:
    Parameters will be passed as follows
    select F_FUNCTION_NAME(sysdate,1) from dual;
    And what do you expect as the output?
  • 6. Re: Add hours and minutes to date
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    remove GMT and use the colon between hours and minutes in this way:
    ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
    
    SELECT SYSDATE
         , CAST (CAST (SYSDATE AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE '+4:00' AS DATE) offset_date
      FROM DUAL;
    
    SYSDATE                OFFSET_DATE           
    ---------------------- ----------------------
    06-MAR-2013 12:08:01   06-MAR-2013 15:08:01  
    Regards.
    Al
  • 7. Re: Add hours and minutes to date
    Sajeeva Lakmal Newbie
    Currently Being Moderated
    Sorry, parameters are incorrect, It should be like this
    select F_FUNCTION_NAME(sysdate,'GMT+1400') from dual;
    and the output should be a date value ( sysdate + 14 hours)

    I will update the previous post.
  • 8. Re: Add hours and minutes to date
    jeneesh Guru
    Currently Being Moderated
    Sajeeva Lakmal wrote:
    Sorry, parameters are incorrect, It should be like this
    select F_FUNCTION_NAME(sysdate,'GMT+1400') from dual;
    and the output should be a date value ( sysdate + 14 hours)

    I will update the previous post.
    What is the significance of GMT, here?

    You could just pass "14" right?
    What difference you expect if the call was like
    F_FUNCTION_NAME(sysdate,'IST+1400')
    And as already shown, if you want the time in GMT, you need to pass only the string 'GMT'
    cast (cast (sysdate as timestamp with time zone) at time zone 'GMT' as date)
    Edited by: jeneesh on Mar 6, 2013 4:48 PM
  • 9. Re: Add hours and minutes to date
    Sajeeva Lakmal Newbie
    Currently Being Moderated
    Hi,
    'GMT+1400' is the value passed by the application. "GMT" doesn't mean anything in this case, objective should be to add the right most value to the first parameter. Anyway I got it done using CAST.

    Thank You.
  • 10. Re: Add hours and minutes to date
    Sajeeva Lakmal Newbie
    Currently Being Moderated
    Thank you.
    create or replace
    Function         F_FUNCTION_NAME(dt date,timeZoneOffset   in   varchar2)
    return date
    As
        offset  varchar2(9);    
    Begin
        Offset := (To_Char(Substr(Substr(Timezoneoffset,4),0,Length(Substr(Timezoneoffset,4))-2)||':'||Substr(Substr(Timezoneoffset,4),-2)));
        return (Cast (Cast (Sysdate As Timestamp With Time Zone) At Time Zone Offset As Date));
        Exception  When Others Then Return Null ;
    End;
  • 11. Re: Add hours and minutes to date
    Stew Ashton Expert
    Currently Being Moderated
    You say the input date is already in GMT.

    So you need to convert the date to a "timestamp with time zone" datatype, with the time zone being GMT.

    [ Warning! "Cast (Sysdate As Timestamp With Time Zone)" will give you a "timestamp with time zone" datatype,
    but the time zone may not be GMT! ]

    Then you can use the FROM_TZ function to convert to the time zone you want, then CAST the result as a date.

    Here's a SELECT statement that walks you through the steps:
    alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
    select 
    sysdate now,
    cast(sysdate as timestamp) now_as_timestamp,
    from_tz(cast(sysdate as timestamp), '0:00') at time zone '3:00' now_with_timezone,
    cast(from_tz(cast(sysdate as timestamp), '0:00') at time zone '3:00' as date) now_with_tz_as_date
    from dual;
    
    NOW                   NOW_AS_TIMESTAMP             NOW_WITH_TIMEZONE                   NOW_WITH_TZ_AS_DATE 
    --------------------- ---------------------------- ----------------------------------- ---------------------
    2013/03/06 03:30:09   06/03/13 03:30:09,000000000  06/03/13 06:30:09,000000000 +03:00  2013/03/06 06:30:09
    And here's a function that does what I think you want.
    create or replace function date_adjust(p_date in date, p_tz in varchar2) return date
    as
    l_tz varchar2(8) := p_tz||':00';
    begin
    return cast(from_tz(cast(p_date as timestamp), '0:00') at time zone l_tz as date);
    end date_adjust;
    /
    
    select date_adjust(to_date('2013/02/02 12:34:56'), '-3') new_date
    from dual;
    
    NEW_DATE            
    ---------------------
    2013/02/02 09:34:56 
    Edited by: Stew Ashton on Mar 6, 2013 12:35 PM
  • 12. Re: Add hours and minutes to date
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Sajeeva,

    this will work with your input, however GMT can range only from GMT-1200 to GMT+1400. Values out of this range will not work.
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
    
    
    CREATE OR REPLACE FUNCTION f_function_name (dt DATE
     , timezoneoffset IN VARCHAR2               )
       RETURN DATE
    AS
       offset  VARCHAR2 (9) := REGEXP_REPLACE (timezoneoffset, 'GMT(\+|-)([0-9]{1,2})([0-9]{2})', '\1\2:\3');
    BEGIN
       RETURN CAST (FROM_TZ (CAST (dt AS TIMESTAMP), 'GMT') AT TIME ZONE offset AS DATE);
    EXCEPTION
       WHEN OTHERS
       THEN
          RETURN NULL;
    END;
    
    SELECT f_function_name (TO_DATE ('06/03/2013 12:45:00', 'DD/MM/YYYY/ HH24:MI:SS'), 'GMT+400') newdt
      FROM DUAL;
    
    NEWDT                
    ---------------------
    06/03/2013 16:45:00  
    1 row selected.
    
      
    SELECT f_function_name (TO_DATE ('06/03/2013 12:45:00', 'DD/MM/YYYY/ HH24:MI:SS'), 'GMT+1400') newdt
      FROM DUAL;
    
    NEWDT                
    ---------------------
    07/03/2013 02:45:00  
    1 row selected.
    
    
    SELECT f_function_name (TO_DATE ('06/03/2013 12:45:00', 'DD/MM/YYYY/ HH24:MI:SS'), 'GMT-1200') newdt
      FROM DUAL;
    
    NEWDT                
    ---------------------
    06/03/2013 00:45:00  
    1 row selected.
    
    -- This will not work  
    SELECT f_function_name (TO_DATE ('06/03/2013 12:45:00', 'DD/MM/YYYY/ HH24:MI:SS'), 'GMT-1400') newdt
      FROM DUAL;
    
    NEWDT                
    ---------------------
                         
    1 row selected.
    The problem on the last row is related to Oracle not accepting the time zone and throwing this error:
    SELECT SYSTIMESTAMP AT TIME ZONE '-13:00' FROM DUAL;
    
    ORA-01874: time zone hour must be between -12 and 14
    Regards.
    Al

    Edited by: Alberto Faenza on Mar 6, 2013 12:57 PM
  • 13. Re: Add hours and minutes to date
    Stew Ashton Expert
    Currently Being Moderated
    Alberto Faenza wrote:
    EXCEPTION
    WHEN OTHERS
    THEN
    RETURN NULL;
    Any particular reason to hide the exception? That is a bad programming practice in general.
  • 14. Re: Add hours and minutes to date
    chris227 Guru
    Currently Being Moderated
    Offset + 1400 means 14 hours back, right? Otherwise the leading sign must swapped
    declare
    function F_FUNCTION_NAME(dt date, timeZoneOffset in varchar2)
    return date
    is
    l_dt date;
    begin
    l_dt := cast (SYS_EXTRACT_UTC (
              TO_TIMESTAMP_TZ (
                to_char(dt,'DDMMYYYYHH24MISS')
              ||regexp_replace(timeZoneOffset , '^.*?(+|-)(\d{1,2})(\d{2})', '\1\2:\3')
              ,'DDMMYYYYHH24MISSTZH:TZM'
              )
            ) as date);
    return l_dt;
    end;
    begin
    DBMS_OUTPUT.PUT_LINE(to_char(sysdate,'DD-MM-YYYY HH24:MI:SS'));
    DBMS_OUTPUT.PUT_LINE(to_char(F_FUNCTION_NAME(sysdate, 'GMT+1400'),'DD-MM-YYYY HH24:MI:SS'));
    DBMS_OUTPUT.PUT_LINE(to_char(F_FUNCTION_NAME(sysdate, 'GMT-400'),'DD-MM-YYYY HH24:MI:SS'));
    end;
    
    06-03-2013 06:29:00
    05-03-2013 16:29:00
    06-03-2013 10:29:00
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points