CallTime - How to convert to Date

[mgcristino]

Does anyone have a Oracle function to convert the Genesys Time to Timezone Date/Time with Daylight Saving Tim

[Haldane]

I have a fuction somewhere I'll send on to you.

[Scottyjohn]

Hi,
If Genesys time is in UTC, here are a couple of Oracle SQL statements to cenvert to something more meaningful....

Convert to DD/MM/YYYY : TO_CHAR(TO_DATE('01.01.70','DD.MM.RR')+(<yourtimefieldname>/(60*60*24)),'DD/MM/YYYY')
Convert to HH24:MI:SS  : TO_CHAR(TO_DATE('01.01.70','DD.MM.RR')+(<yourtimefieldname>/(60*60*24)),'HH24:MI:SS')

Hope these help

[Jeff]

I am in UTC+3 timezone. Where do I add this?

[tony]

[quote author=Jeff link=topic=1822.msg5891#msg5891 date=1158036881]
I am in UTC+3 timezone. Where do I add this?
[/quote]

Guessing it would be:

Convert to DD/MM/YYYY : TO_CHAR(TO_DATE('01.01.70','DD.MM.RR')+((<yourtimefieldname>/(60*60*24)+10800)),'DD/MM/YYYY')
Convert to HH24:MI:SS  : TO_CHAR(TO_DATE('01.01.70','DD.MM.RR')+((<yourtimefieldname>/(60*60*24)+10800)),'HH24:MI:SS')

Although not ideal for daylight saving, if you need it...

Tony

[Haldane]

Jeff,
    Here's the function that I promised: You'll notice that I add 1hour for daylight savings "temp_utc := i_utc + 3600" so you can probably use this and add 3 hours for GMT3.



CREATE OR REPLACE FUNCTION "TO_GMT"  (i_utc NUMBER) RETURN DATE IS
 
  result DATE; -- The return result
  temp_utc NUMBER;

BEGIN

    IF (i_utc IS NULL) OR (i_utc = 0) THEN -- Don't Process with a null value
    result := NULL;

    Else

      temp_utc := i_utc + 3600;
      --temp_utc := i_utc;

    result := NEW_TIME(TO_DATE(TO_CHAR(TO_DATE _
    ('01/01/1970','DD/MM/YYYY')+ FLOOR(temp_utc/86400) _
    ,'DD/MM/YY ') ||FLOOR(MOD(temp_utc,86400)/3600) || ':' || _
                  FLOOR(MOD(temp_utc,3600)/60) || ':' || MOD(temp_utc,60), _
                  'DD/MM/YY HH24:MI:SS'), _
                'GMT', _
                'GMT');

    END IF;

    RETURN(result);

END TO_GMT;