TIMESTAMP in DB table compare with select-options on date

[bhaskar_nagula]

Hi,

There is a timestamp in DB table but in my program, there is a select-options for date field only.

Now, I have to fetch records from DB table where timestamp = s_date.

You'll get an idea of what I'm trying to do but I'm looking for a shortcode that is directly on the SELECT query and READ TABLE as well because there is a performance issue on the below code.

 SELECT * FROM ztable
 INTO TABLE @DATA(gt_tab)
 ORDER BY ztstmp DESCENDING.
 IF sy-subrc EQ 0.
 LOOP AT gt_tab INTO DATA(ls_tab).
 CONVERT TIME STAMP ls_tab-ztstmp TIME ZONE sy-zonlo INTO DATE DATA(t_date).
 IF NOT t_date IN s_date.
 DELETE gt_tab FROM ls_tab. "INDEX sy-index.
 ENDIF.
 ENDLOOP.
 ENDIF.

[FredericGirod]

https://answers.sap.com/questions/8079633/select-query-on-time-stamp.html

...

[Sandra_Rossi]

Please edit your answer, select your code and press the "CODE" button to make it correctly colorized/indented, so that it's easier for anyone to read it. Thank you.

[Sandra_Rossi]

If there's a performance issue, it's only either SELECT or DELETE (because how you made your code, it scans the whole internal table at each DELETE).

The optimum solution would be completely different but a little time-consuming to explain, the other solution should be satisfying:

DELETE gt_tab USING KEY loop_key.

(DELETE gt_tab without USING KEY loop_key can also work but I think it's less legible)

[raymond_giuseppi]

You could create a RANGE of timestamp and in a simple loop convert the SELECT-OPTIONS on date to this range. Then use the range in the SELECT statement. This should prevent reading the whole database table.

(Use a CONVERT statement on LOW and HIGH value of the both tables, just set time values of 00:00:00 and 23:59:59 in a logical way, example for a I/BT/date1/date2 create timstp1 with 00:00:00 and timstp2 with 23:59:59 to build a I/BT/timstp1/timstp2. If you don't want to handle exotic selections from users, use FM SELECT_OPTIONS_RESTRICT to only allow include, I/EQ, I/LE, I/LT, I/GE, I/GT, I/NB and I/BT, options. or only convert thoses options to where criteria and then delete records with wrong date with the full select-options)