SAP-ABAP-SQL语句中CAST字段类型转换示例，CONCAT连接示例，SUBSTRING截断示例

本文只针对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 SINGLE
         FROM demo_expressions
         FIELDS CAST( CAST( DIV( timestamp1 , 1000000 )
                        AS CHAR )
                  AS DATS ) AS date,
                CAST( SUBSTRING( CAST( timestamp1
                                   AS CHAR ), 9, 6 )
                  AS TIMS ) AS time
         INTO @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转为char
    FROM  MSEG
    INNER  JOIN LIPS ON MSEG~VBELN_IM = LIPS~VBELN
    INNER  JOIN VBAK ON LIPS~VGBEL = VBAK~VBELN
    WHERE MSEG~BWART IN ( 'Z21' , 'Z22' )
    AND   MSEG~VBELN_IM <> ''
    AND   MSEG~WERKS = @P_BUKRS
    AND   MSEG~CPUDT_MKPF IN @S_BUDAT
    INTO  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 SINGLE
         FROM demo_expressions
         FIELDS CAST( CAST( DIV( timestamp1 , 1000000 )
                        AS CHAR )
                  AS DATS ) AS date,
                CAST( SUBSTRING( CAST( timestamp1
                                   AS CHAR ), 9, 6 )
                  AS TIMS ) AS time
         INTO @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将数据截取出来，然后再强转为时间类型。