SQL Command-Date time in where clause

[Former Member]

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

[Sandeep_Panghal]

So what's the issue ?

[Clemenss]

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

[matt]

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.

[Former Member]

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

[matt]

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

[Former Member]

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

[matt]

That is a relief. Thank-you.