CallTime - How to convert to Date

General Discussion
1

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

2

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

2 Likes
3

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’)+(/(606024)),‘DD/MM/YYYY’)
Convert to HH24:MI:SS : TO_CHAR(TO_DATE(‘01.01.70’,‘DD.MM.RR’)+(/(606024)),‘HH24:MI:SS’)

Hope these help

1 Like
4

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

5

Guessing it would be:

Convert to DD/MM/YYYY : TO_CHAR(TO_DATE(‘01.01.70’,‘DD.MM.RR’)+((/(606024)+10800)),‘DD/MM/YYYY’)
Convert to HH24:MI:SS : TO_CHAR(TO_DATE(‘01.01.70’,‘DD.MM.RR’)+((/(606024)+10800)),‘HH24:MI:SS’)

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

Tony

6

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;

2 Likes