topic Re: How to use between in SELECT statements in Application Development and Automation Discussions

How to use between in SELECT statements

nandishm — Tue, 11 Nov 2014 08:45:57 GMT

In one of my requirement, I need to check material is valid or not by using SY_DATUM .

I have two fields in table START_DATE and END_DATE .

I need to check sy-datum inside those two fields .

First I wrote : -

SELECT * FROM XYZ INTO ABC

WHERE

sy-datum BETWEEN start_date AND end_Date .

Its not working

I also tried

SELECT * FROM XYZ INTO ABC

WHERE

start_date GE sy-datum AND end_date LE sy-datum.

This also not working.... for all records sy-subrc - 4 only ...

Is there any way to achieve it.... without using another if statement, any way do it in select statement only

Thanks

Re: How to use between in SELECT statements

Former Member — Tue, 11 Nov 2014 08:51:29 GMT

Hi,

Please try below logic

SELECT single * FROM XYZ INTO ABC WHERE start_date le sy-datum AND end_date ge sy-datum.

Thanks,

Sree

Re: How to use between in SELECT statements

Former Member — Tue, 11 Nov 2014 08:52:27 GMT

Try to use a range:

DATA: r_data TYPE RANGE OF sy-datum.

DATA: s_data LIKE LINE OF r_data.

s_data-option = 'BT'.

s_data-sign = 'I'.

s_data-low = start_date.

s_data-high = end_date.

APPEND s_data TO r_data.

select fields...

from table

where start_date in r_data

and end_date in r_data.

Re: How to use between in SELECT statements

pavanm592 — Tue, 11 Nov 2014 08:52:53 GMT

Hi Nandish,

Once go through the sap help for Between statement, try like this

SELECT * from <Table Name>

into table <Internal Table>

WHERE <Database fieldname> BETWEEN <start_date> AND <End_date>.

Ex:

SELECT carrid connid fldate

FROM sflight

INTO CORRESPONDING FIELDS OF TABLE sflight_tab

WHERE fldate BETWEEN sy-datum AND date.

Regards,

Pavan

Re: How to use between in SELECT statements

Former Member — Tue, 11 Nov 2014 09:02:56 GMT

Hi,

Do like this

Re: How to use between in SELECT statements

Former Member — Tue, 11 Nov 2014 09:26:13 GMT

HI,

working fine for me

SELECT * FROM mara INTO CORRESPONDING FIELDS OF TABLE lt_mara WHERE ersda BETWEEN '20140506' AND '20140508'.

this is surely something you can google in the forum

regards

Stefan Seeburger

Re: How to use between in SELECT statements

vamsilakshman_pendurti — Tue, 11 Nov 2014 09:28:30 GMT

Hi Nandish,

Put a break point before the select statement and check the DATE format, which you want to placed in where condition.

If that format is mismatched then also it will not work....

for ex:

sy-datum = 20141111.

But your input format may like this ---> 11.11.2014

Then it will be mismatched so Debug and check it once

Regards ,

Vamsi.

Re: How to use between in SELECT statements

nandishm — Tue, 11 Nov 2014 09:30:35 GMT

I think ersda field of ur table ...

But in my case sy-datum is not field of table .

Re: How to use between in SELECT statements

PraveenDwivedi — Tue, 11 Nov 2014 09:44:36 GMT

Hi Nandish

In your first query statement, in the where clause any table field name is to be given, sy-datum will not work.

Your second query statement should work as such.

But as you mentioned sy-subrc = 4; this means there is no data matching to your selection criteria.

I see that your query is wrong and it should be like:

SELECT * FROM XYZ INTO ABC

WHERE

start_date LE sy-datum AND end_date GE sy-datum.

start date is less than current date and en date is greater than current date. This will give you the result.

Regards

Praveen

Re: How to use between in SELECT statements

Former Member — Tue, 11 Nov 2014 09:49:35 GMT

Hi,

SELECT * FROM XYZ INTO ABC

WHERE

start_date GE sy-datum AND end_date LE sy-datum.

in this case starting date needs to be higher or same than end date.... this cant be. most likly you have to write it the other way round...

sry for first version - not both options are working - the 1 with between is completly wrong, becausethe table field needs to be on the left side and needs to be compared with 2 other fields. and you done it the other way round.

regards

Stefan Seeburger

Re: How to use between in SELECT statements

nagarjun_kalletla — Tue, 11 Nov 2014 09:54:39 GMT

Hi Nandash

Select * from (DTable ) into (ITable) where Start_date le sy-datum and End_date = sydatum.

This will get you the records which are created before current date and end_date is your second condition you can mention GE or le ....

Meaning Record has been created well before your select query right ...

So Start date should be LE (Meaningful right ??) if you use GE I think Start_date will not have dates which are greater than current ....

Re: How to use between in SELECT statements

former_member639158 — Tue, 11 Nov 2014 13:11:57 GMT

Hello NANDASH,

read this article it's can help you :

http://www.erpgreat.com/abap/difference-between-select-options-ranges.htm

----------------

cordially