topic Re: Select query with offset based on timestamp in Application Development and Automation Discussions

Select query with offset based on timestamp

former_member225134 — Thu, 31 May 2018 11:29:20 GMT

Hi All,

In standard table the date and time field are storing in same field like

31.05.2018 16:46:40.. In my requirement i wan to delete the latest records based on time and date..Before conversion routine the value is stored in lv_date1(diff format).

DELETE FROM TABLE WHERE created_by EQ sy-uname and created_at eq lv_date .

Because of conversion routine it is throwing sy-subrc = 4, this is not deleting any records..

Pleas give me suggestion to delete records based on time(greater than time).

Thanks.

Re: Select query with offset based on timestamp

former_member259807 — Thu, 31 May 2018 11:34:45 GMT

Instead of EQ use GT (or GE)? Have you tried that?

Re: Select query with offset based on timestamp

former_member225134 — Thu, 31 May 2018 11:36:14 GMT

yeah tried that ..its also not working

Re: Select query with offset based on timestamp

former_member391501 — Thu, 31 May 2018 11:45:18 GMT

Have mentioned table in ur query????

Re: Select query with offset based on timestamp

former_member225134 — Thu, 31 May 2018 11:49:47 GMT

yeah mentioned logwin/rec_inf table (created_at field)

Re: Select query with offset based on timestamp

Former Member — Sat, 02 Jun 2018 14:44:51 GMT

Hi,

Try to do the following:

Get data from table into an internal table IT_TAB_DATA.
Loop over the records of the internal table IT_TAB_DATA one by one and process each record.
While processing each record split the data in the date filed using the SPLIT command using SPACE and get time in a separate variable for example lv_time . (as i see the data is like 31.05.2018 16:46:40 , so here there is a space between date and time)
Compare lv_time with the Time you need as the cutoff time for filtering records.
If the record satisfies the condition, ignore it else store in a separate internal table IT_FINAL.
This way you will end preparing a final internal table IT_FINAL with the list of records you want to delete.
Now Finally , use this statement to achieve your final result :
DELETE <target> FROM TABLE IT_FINAL .. Here target will be your TABLE name from which you want to delete the records.

Thanks and Hope this helps .

Hemant