Select records with a specified date period

[Former Member]

Hi all,

In my selection screen, i allow user to specify any date period, e.g., 08.01.2004 to 23.04.2008.

And, the db records may be like the below table, in such case, the three records should be selected with the above selection criteria.

My question is that how could I correctly select the records from DB that fall within the specified time period?

Many Thanks !

ID | BEGDA | ENDDA

1 | 09.09.2005 | 11.11.2006

2 | 12.11.2006 | 07.07.2008

3 | 01.01.2002 | 12.12.2007

[Former Member]

Try This

SELECT-OPTIONS: p_date FOR sy-datum NO EXTENSION.

SELECT *

FROM TABLE <tablename>

INTO TABLE ITAB

WHERE begda GE p_date-low

AND ENDDA LE p_date-high.

[Former Member]

Thanks.

But what if there are another 2 records, the 4th and 5th records should be selected too:

selection criteria: 08.01.2004 to 23.04.2008

ID | BEGDA | ENDDA

1 | 09.09.2005 | 11.11.2006

2 | 12.11.2006 | 07.07.2008

3 | 01.01.2002 | 12.12.2007

4 | 01.01.2002 | 12.12.2009

5 | 09.01.2004 | 12.12.2009

[Former Member]

SELECT *

FROM TABLE <tablename>

INTO TABLE ITAB

WHERE begda GE p_date-low

AND ENDDA LE p_date-high.

selection criteria: 08.01.2004 to 23.04.2008

ID | BEGDA | ENDDA

1 | 09.09.2005 | 11.11.2006

2 | 12.11.2006 | 07.07.2008

3 | 01.01.2002 | 12.12.2007

4 | 01.01.2002 | 12.12.2009

5 | 09.01.2004 | 12.12.2009

The 4th and 5th records won't get selected as the last where clause will get failed for 4th and 5th record

ENDDA LE p_date-high ==> 12.12.2009 LE 23.04.2008

[faisal_altaf2]

Hi, Macy.

Test the following Code hope this will work for you.

SELECT * FROM TABLE 
INTO CORRESPONDING FIELDS OF TABLE ITAB
WHERE begda BETWEEN p_date-low and p_date-high
  AND ENDDA BETWEEN p_date-low and p_date-high.

Please Reply if any Issue,

Kind Regards,

Faisal

[vinod_vemuru2]

HI,

Try in this way.

SELECT-OPTIONS: s_date FOR sy-datum NO EXTENSION.

SELECT *
FROM TABLE dbtab
INTO TABLE i_dbtab
WHERE begda GE s_date-low
OR ENDDA LE s_date-high.

This query will give u records for which begin date is GE low value OR enddate is LE high value.

Thanks,

Vinod.

[Former Member]

Hi Check this Statement

selection criteria: 08.01.2004 to 23.04.2008

ID | BEGDA | ENDDA 
1 | 09.09.2005 | 11.11.2006
2 | 12.11.2006 | 07.07.2008
3 | 01.01.2002 | 12.12.2007
4 | 01.01.2002 | 12.12.2009 
5 | 09.01.2004 | 12.12.2009

SELECT *
FROM TABLE <tablename>
INTO TABLE ITAB
WHERE begda LE p_date-high 
AND ENDDA GE p_date-low .

This select all the above records which are active on any one or more days in this period.

SELECT *
FROM TABLE <tablename>
INTO TABLE ITAB
WHERE begda LE p_date-low 
AND ENDDA GE p_date-high.

ID | BEGDA | ENDDA 
2 | 12.11.2006 | 07.07.2008
4 | 01.01.2002 | 12.12.2009 

This select above records which are active on all the days in this period.

[Former Member]

Thanks all for your reply.

So how to write a single select statement so that the 5 records could be retrieved?

Many Thanks!

[faisal_altaf2]

Hi, Macy,

What do you mean can you please Explain ?

Kind Regards,

Faisal

[murat_kaya]

Hi Macy,

SELECT-OPTIONS: p_date FOR sy-datum NO EXTENSION.

SELECT *

FROM TABLE <tablename>

INTO TABLE ITAB

WHERE ENDDA GE p_date-low

AND begda LE p_date-high.

this returns all the data you require.

regards,

Murat Kaya

[faisal_altaf2]

Hi, Macy

Total Questions:   	 144 (100 unresolved)

Please Close your Open Questions.

Kind Regards,

Faisal