topic Re: SQL Command-Date time in where clause in Application Development and Automation Discussions

SQL Command-Date time in where clause

Former Member — Sun, 23 Jan 2011 13:25:26 GMT

Hi friends

complecated SQL command for fetching records using where clause of Date and Time - (if using Select option).

Very Helpfull.

types: begin of ty_vbak,
        vbeln type vbak-vbeln,
        erdat type vbak-erdat,
        erzet type vbak-erzet,
       end of ty_vbak.
data: lt_vbak type table of ty_vbak.
field-symbols: <fs_vbak> type ty_vbak.

select vbeln erdat erzet into table lt_vbak
from vbak
where ( ( erdat = '20081123' and erzet >= '155127' ) or erdat > '20081123' )  "lower limit date and time
and   ( ( erdat = '20081124' and erzet <= '190000' ) or erdat < '20081124' ).   "upper limit date and time

sort lt_vbak by erdat erzet.
if sy-subrc eq 0.
  loop at lt_vbak assigning <fs_vbak>.
    write: / <fs_vbak>-vbeln ,<fs_vbak>-erdat ,<fs_vbak>-erzet .
  endloop.
endif.

Regards

Vinayak Sapkal

Edited by: Matt on Jan 23, 2011 6:26 PM - added tags

Re: SQL Command-Date time in where clause

Sandeep_Panghal — Sun, 23 Jan 2011 13:29:00 GMT

So what's the issue ?

Re: SQL Command-Date time in where clause

Clemenss — Sun, 23 Jan 2011 14:16:57 GMT

Hi Vinayak,

some of the newer functionalities use time stamp fields. Time stanp fields have both date and time and are by definition transformed to UTC so that they can be compared between different local time zones. If the database table uses a timestamp, it is much easier.

The way you do it is correct and can not be simplified.

To make the programming a little more transparent, you can proceed like

DATA:
  lv_lower TYPE timestamp,
  lv_upper TYPE timestamp.
  CONVERT DATE '20081123' TIME '155127' 
    INTO TIME STAMP lv_lower TIME ZONE sy-zonlo.
  CONVERT DATE '20081124' TIME '190000' 
    INTO TIME STAMP lv_upper TIME ZONE sy-zonlo.

SELECT vbeln erdat erzet INTO ls_vbak
   from vbak.
  CONVERT DATE ls_vbak-erdat TIME ls_vbak-erzet 
    INTO TIME STAMP lv_timestamp TIME ZONE sy-zonlo.
  CHECK lv_timestamp BETWEEN lv_lower AND lv_upper.
  APPEND ls_vbak TO lt_vbak.
ENDSELECT.

Regards,

Clemens

Re: SQL Command-Date time in where clause

matt — Sun, 23 Jan 2011 17:29:15 GMT

But don't proceed like that, as it shifts the where selection to within the loop, and will hammer performance (on any suitable large table).

I'd still like the original poster specify what his problem is.

Re: SQL Command-Date time in where clause

Former Member — Mon, 24 Jan 2011 02:42:01 GMT

Hi Vinayak,

Why dont you try like this,


types: begin of ty_vbak,
        vbeln type vbak-vbeln,
        erdat type vbak-erdat,
        erzet type vbak-erzet,
       end of ty_vbak.
data: lt_vbak type table of ty_vbak.
data: erdat_low type vbak-erdat.
data: erdat_high type vbak-erdat.

select vbeln erdat erzet into table lt_vbak
from vbak 
where erdat between erdat_low and erdat_high.

delete it_vbak where erzet >= '155127' and erdat = erdat_low.
delete it_vbak where erzet <= '190000' and erdat = erdat_high.

-Dileep .C

Re: SQL Command-Date time in where clause

matt — Mon, 24 Jan 2011 05:37:30 GMT

Why not leave it as it is? The where clause is perfectly ok. All other suggestions are slower. Moved to performance so others can despair...

matt

Re: SQL Command-Date time in where clause

Former Member — Mon, 24 Jan 2011 05:45:01 GMT

Thanks mat, i also found where clause is faster than other suggestions. I am using where clause only.

I tried to explore any new techniques available, but found where clause is only right answer as performance per say.

Regards

Vinayak Sapkal

Re: SQL Command-Date time in where clause

matt — Mon, 24 Jan 2011 05:46:13 GMT

That is a relief. Thank-you.