topic Re: Select Statement to check date between dates in Application Development and Automation Discussions

Select Statement to check date between dates

former_member207873 — Thu, 31 Jul 2014 13:21:29 GMT

Hi Experts,

I wanted to find data from table based on the dates. But it is not fetching any data.

SELECT UKURS FROM TCURR INTO TABLE IT_UKURS
WHERE GDATU GE <FS_OUTTAB>-BUDAT AND GDATU LE LV_LAST_DAY.

** I also tried BETWEEN Statement still not working.

My <FS_OUTTAB>-BUDAT and
LV_LAST_DAY is of the format YYYYMMDD.

BR.

Re: Select Statement to check date between dates

Former Member — Thu, 31 Jul 2014 13:26:52 GMT

Hi,

Try Declaring Lv_Last_day as date Type like

Lv_Lst_day type sy_datum.

Re: Select Statement to check date between dates

former_member207873 — Thu, 31 Jul 2014 13:40:56 GMT

No, the issue is with field GDATU in Table TCURR. It is not of date type. So how can I use the select statement with a between statement ?

Re: Select Statement to check date between dates

Former Member — Thu, 31 Jul 2014 13:49:17 GMT

You need to convert <FS_OUTTAB>-BUDAT and LV_LAST_DAY in the same format of GDATU

Max

Re: Select Statement to check date between dates

former_member207873 — Thu, 31 Jul 2014 13:53:35 GMT

Hi Max,

Thank you. I converted both into inverted format. Still not fetching any data.

CONVERT DATE LV_LAST_DAY INTO INVERTED-DATE LV_LAST_DAY.
CONVERT DATE <FS_OUTTAB>-BUDAT INTO INVERTED-DATE FROM_DATE.

SELECT UKURS FROM TCURR INTO TABLE IT_UKURS
WHERE GDATU GE FROM_DATE AND GDATU LE LV_LAST_DAY.

BR.

Re: Select Statement to check date between dates

former_member201275 — Thu, 31 Jul 2014 14:09:32 GMT

Then you are using the wrong dates. I wrote this and it worked fine:

   data: it_ukurs type table of tcurr-ukurs.
DATA: fs_outtab-budat(8),
      lv_last_day(8). " is of the format YYYYMMDD

DATA: h_begdat LIKE sy-datum,
      h_enddat LIKE sy-datum.

START-OF-SELECTION.

fs_outtab-budat = '19900101'.
lv_last_day = '20141231'.

CONVERT DATE fs_outtab-budat INTO INVERTED-DATE h_begdat.
CONVERT DATE lv_last_day INTO INVERTED-DATE h_enddat.

SELECT ukurs FROM tcurr INTO TABLE it_ukurs
          WHERE gdatu GE h_enddat AND gdatu LE h_begdat.

Re: Select Statement to check date between dates

Former Member — Thu, 31 Jul 2014 14:14:51 GMT

The rule to convert is easy:


GDATU = '99999999' - DATE (YYYYMMDD)

Try to run this report:


parameters: p1 type sy-datum,
                  p2 type sy-datum.

 data date_c(8) type c.
 data p1_int(8) type c.
 data p2_int(8) type c.


   move p1 to date_c.
   p1_int = '99999999' - date_c.

   write: p1, p1_int.

   move p2 to date_c.
   p2_int = '99999999' - date_c.

   skip.
   write: p2, p2_int.

if P1 is greater than P2 => P2 (converted) will be greater than P1 converted

Max

Re: Select Statement to check date between dates

Former Member — Thu, 31 Jul 2014 14:56:26 GMT

hi,

your question should have been how to compare inverted-Dates with normal dates...

convert your date to invert-Date Format. It took me 1 min going into the table looking for conversion exit. use FM convert_exit_invdt_input as shown in screenshot below. Enter your min and max date there and use the FM-results to do your select-statement...

by the way you find convertion exits when klicking on the dataelement and then double click the domain. It shows that convert-exit INVDT is used. so ther always is a CONVERSION_EXIT_INVDT_INPUT and CONVERSION_EXIT_INVDT_OUTPUT. you can use this FM to figure out how SAP does the conversion.

regards

Stefan Seeburger

Re: Select Statement to check date between dates

former_member201275 — Thu, 31 Jul 2014 14:58:32 GMT

Is your issue resolved? Did any of the replies help or answer your question? Please mark accordingly and close.

Re: Select Statement to check date between dates

former_member207873 — Fri, 01 Aug 2014 04:58:42 GMT

Thank you .