Select query with offset based on timestamp

[former_member225134]

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.

[former_member225134]

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.

[former_member259807]

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

[former_member225134]

yeah tried that ..its also not working

[former_member391501]

Have mentioned table in ur query????

[former_member225134]

yeah mentioned logwin/rec_inf table (created_at field)

[Former Member]

Hi,

Try to do the following:

1. Get data from table into an internal table IT_TAB_DATA.
2. Loop over the records of the internal table IT_TAB_DATA one by one and process each record.
3. 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)
4. Compare lv_time with the Time you need as the cutoff time for filtering records.
5. If the record satisfies the condition, ignore it else store in a separate internal table IT_FINAL.
6. This way you will end preparing a final internal table IT_FINAL with the list of records you want to delete.
7. Now Finally , use this statement to achieve your final result :
8. 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