Saturday, March 5, 2016

Different standard sql functions related time, date and timezone

Different standard sql functions related time, date and timezone





select systimestamp at time zone 'GMT' current_time_in_utc     from dual;


SELECT TZ_OFFSET('Asia/Kuala_Lumpur') FROM DUAL;


select TZ_OFFSET(sessiontimezone) from dual;


ALTER SESSION SET TIME_ZONE = '+08:00';


SELECT * from V$TIMEZONE_NAMES where tzname like '%Asia%';
 
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual;


select LOCALTIMESTAMP,current_timestamp,dbtimezone,sessiontimezone,systimestamp  from dual;


select LOCALTIMESTAMP at time zone  TZ_OFFSET(sessiontimezone) FROM DUAL;


select LOCALTIMESTAMP-systimestamp  from dual;


     
select sys_extract_utc(current_timestamp) from dual;


select systimestamp at time zone 'GMT' current_time_in_utc     from dual;


SELECT TZ_OFFSET('Asia/Kuala_Lumpur') FROM DUAL;


SELECT * from V$TIMEZONE_NAMES where tzname like '%Asia%';
  
select LOCALTIMESTAMP,current_timestamp,dbtimezone,sessiontimezone  from dual;


 select * from all_objects where object_name like 'FND%PROFILE%OP%' and object_type='TABLE';
      
select * from FND_PROFILE_OPTIONS where PROFILE_OPTION_NAME like '%CLIENT_TIMEZONE_ID%';


select * from FND_PROFILE_OPTION_VALUES where PROFILE_OPTION_ID=3773;
        
               "SQL="SELECT  '(GMT ' ||rtrim(tz_offset(timezone_code),chr(0))


|| ') ' || name \"TimeZone\", upgrade_tz_id


INTO :visible_option_value, :profile_option_value


FROM FND_TIMEZONES_VL


WHERE enabled_flag = 'Y'


ORDER BY tz_offset(timezone_code), name"


COLUMN="\"TimeZone\"(*)"";


 


select * from FND_TIMEZONES_VL where upgrade_tz_id=160;


select * from FND_TIMEZONES_VL where name like 'Cal%';


 


select LOCALTIMESTAMP at time zone tz_offset('Asia/Calcutta') from dual;


 


SELECT  '(GMT ' ||rtrim(tz_offset(timezone_code),chr(0))


|| ') ' || name, upgrade_tz_id


FROM FND_TIMEZONES_VL


WHERE enabled_flag = 'Y' ORDER BY tz_offset(timezone_code),name


 

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete