Reports (BIP) – Query to get correct time zone

When an activity in learning is created with a start time and end time, the time is stored in UTC format in the data base. When any reporting is done on this data, the output is incorrect as time is shown in UTC format which is different from user time format.

This is applicable for all transactions. Please use the below workaorund to get the correct date time in the SQL:

select wlif.start_date
      ,wlaf.time_zone
      ,TZ_OFFSET(wlaf.time_zone) time_zone_offset
      ,(FROM_TZ(CAST(wlif.start_date AS TIMESTAMP),(case when TZ_OFFSET(wlaf.time_zone)     like '+%'
then '-'||substr(TZ_OFFSET(wlaf.time_zone),2)
else '+'||substr(TZ_OFFSET(wlaf.time_zone),2)
end
))) actual_start_date
      ,TO_CHAR(CAST(wlif.start_date AT TIME ZONE wlaf.time_zone AS DATE),'HH:Mi AM','nls_date_language=American') actual_start_time
 from wlf_learning_items_f wlif
    , wlf_li_activities_f wlaf
where learning_item_number= 'OLC245017'
  and wlif.learning_item_id = wlaf.learning_item_id
  and trunc(sysdate) between wlif.effective_start_date and wlif.effective_end_date
  and trunc(sysdate) between wlaf.effective_start_date and wlaf.effective_end_date