2747000 - Learning Custom Reports: ANSI Standards and Handling Dates and Datetime Conversions

The article will present steps to handle Time and Date conversion within Learning Report Designer.

Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

SAP SuccessFactors Learning

Handling Dates and Datetime Conversions

Time zone conversion can be difficult when you write a custom report in Learning.
In general, please follow this standard: start with UTC and convert the date-time into the correct time zone for the user.

Please see the attachment regarding Standards for Custom Reports with SAP SuccessFactors Learning Report Designer:
ANSI SQL is our mandatory standard for SAP SuccessFactors Learning Report Designer. When you write report SQL in ANSI SQL, we have a higher confidence that those reports will deliver accurate and consistent results. Dates and datetime conversions may require changes in your reports. Please review guidance below on handling dates and datetime conversion.

This applies to date-time fields:

• Date-time fields (as opposed to date fields) appear to report readers in UTC by default. 
• If you apply explicit BIRT time zone conversion to a date-time field, the report honors the conversion: readers of the reports see the date-time converted.

• Do not for example, assume that you can rely on the time zone of the data center. Instead, assume that the raw date-time is in UTC. 
• Date fields (as opposed to date-time fields) should not have a time zone conversion
• For example, assignment date is a date field, so it does not require time zone conversion. 
• Do not use the BIRT operator for time zone conversion on date fields because time zones make sense for date-time fields only.

Steps to use timezone conversion without using Handler Class:

In PRD you can use the below function to convert from UTC to your preferred timezone.
    pkg_tool_get_new_date(column, 'UTC' ,'America/New_York') alias
    
    pkg_tool_get_new_date - This is the package we can use to convert timezone
    column - This is the column name you want to convert (This must be date time field only)
    UTC - This is the current timezone and will always remain the same.
    America/New_York - This is your target timezone you want to convert to.
    alias - This is the alias you want to give to your column.

Example:
SELECT STUD_ID,
CPNT_ID,
pkg_tool_get_new_date(COMPL_DTE, 'UTC', 'America/New_York') COMPL_DTE
FROM pa_cbt_stud_cpnt;

Note:
The target timezone should be from the list of timezones available in the system. This timezone can be checked from the Learning Admin Application.
Login as an Admin and navigate to References > Calendars & Time > Timezone Management > Search and copy the ID

Note: If you would like assistance to develop custom reports or to follow the guidance on this KBA please engage Professional Services Team, a certified partner or ask for assistance through the custom report exchange forum on the community. Please refer to kba #2379690 for more information and links.

LMS, UTC, Date Time, Timezone, ANSI, Conversion, Custom Report HANA PRD plateau report designer zone , KBA , lmsadminui , LOD-SF-LMS-ACT , Accomplishments , LOD-SF-LMS , Learning Management System , LOD-SF-LMS-REP , Reporting Data , How To