Need help with SQL and Date Range

[Former Member]

Hi

I need to run this sql with the user entered date range and go through the data. here is what i have.

***

REPORT ZTEST.

Tables: AUFM.

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE TEXT-000

NO INTERVALS.

SELECT-OPTIONS: begdate FOR AUFM-BUDAT,

enddate FOR AUFM-BUDAT.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM AUFM

WHERE

( BUDAT BETWEEN begDate AND endDate ) AND

( BWART = '261' OR BWART = '262' OR BWART = '101' OR BWART = '102'

OR BWART = '202' OR BWART = '201' ).

if sy-subrc = 0.

write: / AUFM-AUFNR, AUFM-BUDAT.

else.

write:/ 'no rec'.

endif.

endselect.

*******

Right now its not working and while debugging i found out that if the user puts 7/1/2006 the begdate actually shows in debug mode: IEQ2006070100000000 and hence my sql is not even checking the sy-subrc.

Please let me know what I am doing wrong here...

[Former Member]

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE TEXT-000

NO INTERVALS.

SELECT-OPTIONS: begdate FOR AUFM-BUDAT,

enddate FOR AUFM-BUDAT.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM AUFM

WHERE

( BUDAT BETWEEN <b>begDate0(8) AND endDate0(8)</b> ) AND

( BWART = '261' OR BWART = '262' OR BWART = '101' OR BWART = '102'

OR BWART = '202' OR BWART = '201' ).

if sy-subrc = 0.

write: / AUFM-AUFNR, AUFM-BUDAT.

else.

write:/ 'no rec'.

endif.

endselect.

[Former Member]

Hi,

Just change the high lighted code, it should work.Your begdate is select option so the high value will be end date . You dont require one more select option for end date. if you are looking for begin date and enddate then declare them as parameters not select-options.

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE TEXT-000

NO INTERVALS.

SELECT-OPTIONS: begdate FOR AUFM-BUDAT.

<b>*enddate FOR AUFM-BUDAT.</b>

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM AUFM

WHERE

<b>*( BUDAT BETWEEN begDate AND endDate ) AND

BUDAT IN begDate and</b>

( BWART = '261' OR BWART = '262' OR BWART = '101' OR BWART = '102'

OR BWART = '202' OR BWART = '201' ).

if sy-subrc = 0.

write: / AUFM-AUFNR, AUFM-BUDAT.

else.

write:/ 'no rec'.

endif.

endselect.

Hope this helps.

[Former Member]

Hi,

Make the BEGDATE and ENDDATE as a parameter instead of a select-options.

Otherwise..

Make it as one select-options..

Ex..

SELECT-OPTIONS SO_DATE FOR AUFM-BUDAT.

SO_DATE-LOW will be your begdate

SO_DATE-HIGH will be your enddate.

Thanks,

Naren

[Former Member]

Hi,

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE TEXT-000

NO INTERVALS.

SELECT-OPTIONS: begdate FOR AUFM-BUDAT,

enddate FOR AUFM-BUDAT.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM AUFM

WHERE

<b>( BUDAT between begDate-low AND endDate-low ) AND</b>( BWART = '261' OR BWART = '262' OR BWART = '101' OR BWART = '102'

OR BWART = '202' OR BWART = '201' ).

if sy-subrc = 0.

write: / AUFM-AUFNR, AUFM-BUDAT.

else.

write:/ 'no rec'.

endif.

endselect.

Regards

amole

[Former Member]

Instead try like this:

REPORT ZTEST.

data: i_aufm type standard table of aufm,

wa_aufm type aufm.

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE TEXT-000

NO INTERVALS.

Parameters: begdate FOR AUFM-BUDAT,

enddate FOR AUFM-BUDAT.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM AUFM into table i_aufm

WHERE

( BUDAT BETWEEN begDate AND endDate ) AND

( BWART = '261' OR BWART = '262' OR BWART = '101' OR BWART = '102'

OR BWART = '202' OR BWART = '201' ).

if sy-subrc = 0.

loop at i_aufm into wa_aufm.

write: / wa_aufm-AUFNR, wa_aufm-BUDAT.

endloop.

else.

write:/ 'no rec'.

endif.

endif.

[Former Member]

Hi Kabir,

Here is the code:

TABLES: aufm.

SELECTION-SCREEN BEGIN OF BLOCK date WITH FRAME TITLE text-000.

SELECT-OPTIONS: s_date FOR aufm-budat.

SELECTION-SCREEN END OF BLOCK date.

SELECT * FROM aufm

WHERE

budat in s_date AND

( bwart = '261' OR bwart = '262' OR bwart = '101' OR bwart = '102'

OR bwart = '202' OR bwart = '201' ).

IF sy-subrc = 0.

WRITE: / aufm-aufnr, aufm-budat.

ELSE.

WRITE:/ 'no rec'.

ENDIF.

ENDSELECT.

Thanks

Eswar

[Former Member]

Just try:

SELECT * FROM AUFM
  WHERE
  ( BUDAT <b>IN begDate</b> ) AND
  ( BWART = '261' OR BWART = '262' OR BWART = '101' OR
    BWART = '102'
 OR BWART = '202' OR BWART = '201' ).

As well, you don't need enddate.

Rob