Selecting rows within last 30 days

Question

I've got a select statement to get the orders of a reference, but now I need to restrict the results into a range od the last 30 days but i don't know how to do it.

SELECT
    *
FROM AFKO
WHERE AFKO~PLNBEZ = @reference
AND DATEDIFF( DAY, AFKO~GLTRP, @sy-datum ) >= -30
ORDER BY AFKO~GLTRP DESCENDING
INTO TABLE @it_afko.

I've readed that DATEDIFF can make the operatio but SAP says that the function is unknown. I'm trying directly using the - operator:

SELECT
    *
FROM AFKO
WHERE AFKO~PLNBEZ = @reference
AND AFKO~GLTRP - @sy-datum >= -30
ORDER BY AFKO~GLTRP DESCENDING
INTO TABLE @it_afko.

but SAP says Only elementary arithmetic types can be used in arithmetic expressions. The type of AFKO~GLTRP is invalid

How can I get the difference?

The ABAP documentation of Open SQL doesn't show `DATEDIFF` in the list of possible functions, where did you get that? Why don't you calculate a variable equal to `SY-DATUM` minus 30 days and just compare it? Or even just doing it with a [host expression](https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/index.htm?file=abenopen_sql_host_expressions.htm) like `AFKO~GLTRP >= @( sy-datum - 30 )` (since 7.50) ? Or use one of the possible [date functions](https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/index.htm?file=abensql_date_func.htm) (since 7.51) ? — Sandra Rossi, May 11 '20 at 07:45

score 0 · Answer 1 · answered May 11 '20 at 07:49

You need to prepare calculated date before using it in opensql for old versions like below example.

DATA: lv_date   TYPE sy-datum,
      reference TYPE afko-plnbez,
      it_afko   TYPE TABLE OF afko.

CALL FUNCTION 'RP_CALC_DATE_IN_INTERVAL'
  EXPORTING
    date            = sy-datum
    days            = 30
    months          = 0
    signum          = '-'
    years           = 0
 IMPORTING
   calc_date       = lv_date.

SELECT *
  FROM afko
  INTO TABLE @it_afko
 WHERE afko~plnbez = @reference
   AND afko~gltrp >= @lv_date
 ORDER BY afko~gltrp DESCENDING.

score 0 · Accepted Answer · answered May 11 '20 at 07:50

0

Just declare a field and calculate the date accordingly:

DATA(lv_date_minus_30) = sy-datum - 30.

SELECT ...
       WHERE ... gltrp >= lv_date_minus_30

answered May 11 '20 at 07:50

József Szikszai

4,791
3
14
24

Selecting rows within last 30 days

2 Answers2