SELECT on VBAK with DATE and TIME

[former_member295881]

Hello experts,

I've a requirement to write an interface to retrieve data from VBAK table. This interface run as background job. Data is fetch from VBAK based on Date (ERDATE) and Time (ERZET). Here is my SELECT statement.

-----------------------------------------------------------------------------------

select vbeln erdat kunnr bstnk audat bstdk auart
from vbak into table i_vbak
where vbeln in s_vbeln and
erdat in s_date and
erzet in s_time and
auart in s_auart.

-----------------------------------------------------------------------------------

We retrieve details from selection screen in select options, s_date and s_time (apart from s_auart - order type) to use in above SELECT.

In lower range of date we retrieve value from a custom table, which stores last run date (this interface runs hourly). In upper range of date we pass system date (sy-datum).

------------------------------------------------------------------------------------
s_date-low = gv_date. "gv_date has value from custom table

s_date-high = sy-datum.
s_date-sign = c_i.
s_date-option = 'BT'.
append s_date .

------------------------------------------------------------------------------------

Similarly, in lower range of time we retrieve value from a custom table, which stores last run time (this interface runs hourly), and upper range of time has system time (sy-uzeit).

------------------------------------------------------------------------------------

s_time-low = gv_time."gv_time has value from custom table

s_time-high = sy-uzeit.
s_time-sign = c_i.
s_time-option = 'BT'.
append s_time.

------------------------------------------------------------------------------------

This above solution works fine till last job runs for the day at 23:00:00. However, when first job kicks in at 00:00:01 and date changes then SELECT fails. This is because both Date and Time have changed. And worse of all, in time 'lower limit become greater then upper limit'.

Something like this.

s_date-low = 2017/02/01 (Last job ran date, coming from custom table)

s_date-high = 2017/02/02 (Sy-datum (new/changed date))

s_time-low = 23:00:00 (Last job ran time, coming from custom table)

s_time-high = 00:00:02 (Sy-uzeit (new/changed time))

Now I'm wondering what could be the best solution to handle the above situation?

Many thanks in advance.

[bertrand_delvallee]

Hello,

You should do that in 3 steps :

-> 1 Select from dates

select vbeln erdat kunnr bstnk audat bstdk auart 
from vbak 
into table i_vbak
where vbeln in s_vbeln 
and erdat in s_date 
and auart in s_auart.

-> 2 filter by time (in past)

delete table i_vbak where erdat = gv_date and erzet < gv_time

-> 3 filter by time (in future)

delete table i_vbak where erdat >= gv_today and erzet > gv_now

With gv_today and gv_now containg sy-datum and sy-uzeit at the beginning of your program (to keep them constant during execution, no matter how long it runs).

Best regards,

Bertrand

[former_member295881]

@ Bertrand Delvallée

Many thanks for your valuable input. Will modify my code to try your suggest.