本文只针对S/4 HANA 1809 及更高版本有效。
话不多说，先上示例代码，然后再慢慢讲解
REPORT ZLQT_CAST.GET TIME STAMP FIELD DATA(timestamp).DELETE FROM demo_expressions.INSERT demo_expressions FROM @(VALUE #( id = 'X' timestamp1 = timestamp ) ).SELECT SINGLEFROM demo_expressionsFIELDS CAST( CAST( DIV( timestamp1 , 1000000 )AS CHAR )AS DATS ) AS date,CAST( SUBSTRING( CAST( timestamp1AS CHAR ), 9, 6 )AS TIMS ) AS timeINTO @DATA(wa).cl_demo_output=>display( wa ).输出截图
CAST，SUBSTRING，CONCAT联合起来在一个语句中使用
SELECT MSEG~WERKS ,MSEG~CPUDT_MKPF ,MSEG~BUDAT_MKPF ,MSEG~VBELN_IM ,LIPS~VGBEL ,VBAK~BSTNK ,CONCAT( ' ' , CONCAT( MSEG~VBELN_IM , SUBSTRING( CAST( MSEG~BUDAT_MKPF AS CHAR ),1,4 ) ) ) AS AWKEY""为了构造20位char类型字段，与bkpf~awkey连表CONCAT 只能连接两个字段，所以这里用了两个CONCAT语句SUBSTRING只能剪切char类型字段，所以用cast转为charFROM MSEGINNER JOIN LIPS ON MSEG~VBELN_IM = LIPS~VBELNINNER JOIN VBAK ON LIPS~VGBEL = VBAK~VBELNWHERE MSEG~BWART IN ( 'Z21' , 'Z22' )AND MSEG~VBELN_IM <> ''AND MSEG~WERKS = @P_BUKRSAND MSEG~CPUDT_MKPF IN @S_BUDATINTO TABLE @DATA(LT_MSEG) .DELETE ADJACENT DUPLICATES FROM LT_MSEG COMPARING ALL FIELDS .
1.
GET TIME STAMP FIELD DATA(timestamp).作用是获取当前服务器时间，存储到timetamp。DATA(timestamp)是快速定义timestamp 。此时timestamp是P类型，此时该值等于20211020081246。
2.
DELETE FROM demo_expressions.INSERT demo_expressions FROM @(VALUE #( id = 'X' timestamp1 = timestamp ) ).这两句是对透明表demo_expressions做操作，先删除表数据，然后插入一行数据，为后面的sql语句做准备。
3.这段sql语句要从最里面的括号看起
SELECT SINGLEFROM demo_expressionsFIELDS CAST( CAST( DIV( timestamp1 , 1000000 )AS CHAR )AS DATS ) AS date,CAST( SUBSTRING( CAST( timestamp1AS CHAR ), 9, 6 )AS TIMS ) AS timeINTO @DATA(wa).
DIV( timestamp1 , 1000000 )timestamp除1000000取正此时timestamp=20211020 。
然后再看次内层括号，根据上面的运算可以同等转换为
CAST( DIV( timestamp1 , 1000000 ) AS CHAR )CAST( 20211020 AS CHAR )做完此次运算后timestamp=20211020此时为char类型
然后再往外层括号看，此时原代码可以转换为
FIELDS CAST( CAST( DIV( timestamp1 , 1000000 ) AS CHAR ) AS DATS ) AS date,FIELDS CAST( 20211020 AS DATS ) AS date,AS DATS 代表类型转换为DATS类型，AS date代表select 语句中timestamp的别名是date。
至此该部分sql已经解释完了。后部分的sql也很有意思，大家可以尝试解读下。
后半部分的sql是先强转为CHAR类型，再使用SUBSTRING将数据截取出来，然后再强转为时间类型。