How to use between in SELECT statements

[nandishm]

Hi

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

[Former Member]

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

[Former Member]

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.

[Former Member]

Hi,

Do like this

[pavanm592]

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

[Former Member]

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

[nandishm]

I think ersda field of ur table ...

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

[Former Member]

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

[vamsilakshman_pendurti]

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.

[PraveenDwivedi]

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

[nagarjun_kalletla]

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 ....

[former_member639158]

Hello NANDASH,

read this article it's can help you :

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

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

cordially