How to convert time stamp to date and time in CDS views with respect to user time zone?

[former_member712021]

Hello Folks,

I have a requirement to convert the time stamp to date and time in CDS views. I was able to convert it but the real problem is to convert it with respect to the users time zone.

This time zone is located in another table. I have to change the date and time as per the data present in the time zone table and print the output in MM/DD/YYYY HH:MM:SS format.

Please suggest me how I can proceed with this development. I am new to CDS views and I have searched all over the internet trying to replicate the solutions present but none of them provided the satisfactory output.

Thank you in advance.

[Yogananda]

harsha_nihanth

harsha_nihanth

The best way to proceed with this development is to create a CDS View that references the time zone table and then use the SAP HANA SQL function CONVERT_TZ() to convert the Timestamp to the users time zone. The CONVERT_TZ() function takes a timestamp and a time zone as parameters and returns a new timestamp with the time zone applied.

The syntax for the CONVERT_TZ() function is as follows:

CONVERT_TZ(<timestamp>, <timezone1>, <timezone2>)

Where:

<timestamp> - is the timestamp to be converted
<timezone1> - is the source timezone
<timezone2> - is the destination timezone

For example, to convert the timestamp "2020-10-21 10:00:00" from UTC to Pacific Time, you would use the following:

CONVERT_TZ('2020-10-21 10:00:00','UTC','PST')

The output would be:

2020-10-21 03:00:00

You can then use the SAP HANA SQL function TO_CHAR() to format the timestamp as desired.