Skip to Content

In ABAP, all TIME related processing such as converting date/time from one time zone to another time zone requires usage of FM such as

IB_CONVERT_INTO_TIMESTAMP

IB_CONVERT_FROM_TIMESTAMP

As well as usage of syntax such CONVERT..INTO TIMESTAMPE as per ones requirement.

In HANA SQL, this can be achieved using built-in functions such as UTCTOLOCAL, LOCALTOUTC, TO_TIMESTAMP, TO_DATE, TO_TIME etc., in SELECT queries while retrieving the data from DB.

SCENARIO 1:

We have date and time in one time zone ‘X’. This needs to be converted to time zone ‘Y’.

In ABAP this can be achieved using the above mentioned FM’s by passing each record values where as in HANA SQL this can be achieved in SELECT query itself.

In below syntax SO are created in CST and the corresponding time stamp in PST is retrieved.

SELECT VBELN TO_CHAR( UTCTOLOCAL ( LOCALTOUTC ( TO_TIMESTAMP ( CONCAT ( ERDAT, LPAD ( TO_TIME( EZEIT),9 ) ), ‘YYYYMMDD HH24MISS’), ‘CST’ ), ‘PST’), ‘YYYYMDD HH24MISS’) AS TIME_STAMP FROM VBAK

SCENARIO 2:

Fetching date and time as per ABAP DATS/TIMS data type from above calculated time stamp.

SELECT VBELN, TO_DATS(TO_DATE(time_stamp, ‘YYYYMMDD HH24MISS’) ) as DATLO, TO_CHAR(TO_TIME ( time_stamp, ‘YYYYMIDD HH24MMSS’). ‘HH24MISS’) as TIMLO

SCENARIO 3:

We need to convert local system date/time to specific time zone.

SELECT TO_CHAR( UTCTOLOCAL ( LOCALTOUTC ( CURRENT_TIMESTAMP, ‘CST’), ‘PST’), ‘YYYYMMDD HH24MISS’) AS ‘TIME_STAMP’ from DUMMY;

Here CURRENT_TIMESTAMP functions is used to fetch timestamp. Similarly there are other functions to be used as per one’s requirement.

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_UTCDATE

CURRENT_UTCTIME

CURRENT_UTCTIMESTAMP

SCENARIO 4:

ABAP Syntax :

CONVERT DATE LV_DATE TIME LV_TIME INTO TIMESTAMP LV_TIMESTAMP TIME ZONE LV_TIMEZONE.

HANA SQL SYNTAX

SELECT TO_CHAR( LOCALTOUTC ( TO_TIMESTAMP ( CONCAT( TO_DATE ( LV_DATE), LPAD ( TO_TIME ( LV_TIME), 9) ) ), LV_TIMEZONE ) , ‘YYYYMMDD HH24MISS’ ) AS LV_TIMESTAMP FROM DUMMY;

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply