SAP ABAP – NEW OPEN SQL – Query Substring NUMC

There are some fields with data type NUMC that we want to fetch using a query table.

In this case, I want to show you how to split the DATS data type into Year, Month, and Day in a single query.

1. We have a SUBSTRING function in open SQL to cut your data by the length.
   • SUBSTRING( <data> , <start_pointer> , <sub_length> )
2. The return of the SUBSTRING function is data with data type CHAR. So we have to convert it to NUMC again using the CAST function.
   • CAST( <data> AS <data_type> )

Here the example

  TYPES: BEGIN OF ty_data,
           year  TYPE numc4,
           month TYPE numc2,
           day   TYPE numc2,
         END OF ty_data,
         tt_data TYPE TABLE OF ty_data.
  DATA: ls_data TYPE ty_data.

  SELECT SINGLE
    CAST( substring( fldate, 1, 4 ) AS NUMC ) AS year,
    CAST( substring( fldate, 5, 2 ) AS NUMC ) AS month,
    CAST( substring( fldate, 7, 2 ) AS NUMC ) AS day
    FROM sbook
    WHERE fldate NE '00000000'
    INTO @ls_data.

  WRITE:/(8) 'Year' , ':', ls_data-year.
  WRITE:/(8) 'Month' , ':', ls_data-month.
  WRITE:/(8) 'Day' , ':', ls_data-day.

Thank you for reading. I hope you can do it better. See you on the next post.