1 2 Previous Next 16 Replies Latest reply: Mar 7, 2013 2:18 AM by AlbertoFaenza RSS

    Add hours and minutes to date

    Sajeeva Lakmal
      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
          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
            Keith Jamieson
            yes. Its called timestamp with timezone
            • 3. Re: Add hours and minutes to date
              Sajeeva Lakmal
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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