Can I use LIKE on DATS field?

[JMorozowski]

Is it possible to use LIKE on a DATS field in a select statement? Or is there some other operator that I can use? I'm trying to select data based upon current year, but the timestamp field contains 8 digits, I'd like to select it based only on the first four digits.

[sathyags]

won't it be easier to populate 2 vairables (20200101 and 20201231) and use LE and GE ?

[raymond_giuseppi]

Use a range BETWEEN or (LE and GE)

[michael_piesche]

Yes you can use wild card characters on DATS data types in SQL and ABAP.

You have actually several options for select conditions on character datatypes with (SAP) Open SQL.

By the way, the DATS data type in the ABAP dictionary behaves a little different in ABAP then it does in the database. Even though it is just a simple 'character' data type in both systems, in ABAP (Char8) it is 'more' powerful with certain arithmetic operations such as +/-/MOD then it is in a database such as Oracle (VARCHAR2 with length 24). See this blog for further information on operations with DATS in ABAP: Elegance in ABAP: date calculations

jon.morozowski: Therefore, contrary to your description, DATS is not a "timestamp field containing 8 digits". It is a somewhat 'special' character field with 8 characters in ABAP and possibly more in your database. If you are working with a 'real' timestamp field that is of type decimal, than wild cards will not work, but BETWEEN and LE and GE will work, initializing the condition values is a little trickier though. Let me know if you have issues with that instead.

1. LIKE on dats field (here, SQL wild card characters "%" and "_" have to be used, instead of otherwise ABAP wild card characters "*" and "+")

DATA lv_year TYPE datum.
lv_year = sy-datum(4) && '%'.
SELECT *
  FROM dbtable
  INTO TABLE lt_table
  WHERE date LIKE lv_year.

2. BETWEEN on dats field (or GE and LE)

  DATA lv_year_start TYPE datum.
  DATA lv_year_end TYPE datum.
  lv_year_start = sy-datum(4) && '0101'.
  lv_year_end = sy-datum(4) && '1231'.
  SELECT *
    FROM dbtable
    INTO TABLE lt_table
    WHERE date BETWEEN lv_year_start AND lv_year_end.    " var. BETWEEN
  " WHERE date GE lv_year_start AND date LE lv_year_end. " var. GE/LE

3. RANGE on dats field with CP - Contains Pattern (here, 'regular' ABAP wild card characters "*" and "+" are used)

  DATA lr_date TYPE RANGE OF datum WITH HEADER LINE.
  lr_date-sign = 'I'.
  lr_date-option = 'CP'.
  lr_date-low = sy-datum(4) && '*'.
  lr_date-high = ''.
  APPEND lr_date.
  SELECT *
    FROM dbtable
    INTO TABLE lt_table
    WHERE chdat IN lr_date.

4. RANGE on dats field with BT - Between (or with one GE option and one LE option)

DATA lr_date TYPE RANGE OF datum WITH HEADER LINE.
lr_date-sign = 'I'.
lr_date-option = 'BT'.
lr_date-low = sy-datum(4) && '0101'.
lr_date-high = sy-datum(4) && '1231'.
APPEND lr_date.
SELECT *
    FROM dbtable
    INTO TABLE lt_table
    WHERE date IN lr_date.

Please be aware, that the depending on your code environment (eg. classes), the way I have created the range object will not work and you need to create it differently. I chose this coding for simplicity reasons.

[Sandra_Rossi]

It won't hurt if you replace this line:

DATA lr_date TYPE RANGE OF datum WITH HEADER LINE.

with:

DATA: lr_date TYPE RANGE OF datum,
      ls_date LIKE LINE OF lr_date.

so that to get rid of this obsolete and error-prone WITH HEADER LINE.