Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

date and time problem for selection.

Former Member
0 Kudos

Hi ,

I am writing select query as below.

SELECT objectclas objectid FROM cdhdr INTO CORRESPONDING FIELDS OF TABLE mt_cdhdr

WHERE objectclas eq 'STUE_V'

AND udate in udat1

AND utime in utime1.

But in CDHDR if you give the values as below for date and time.

UDATE 04.05.2010 ( From ) UDATE 05.05.2010 ( TO )

UTIME 23:24:21 ( From ) UTIME 06:24:33 ( TO ).

I was not getting the values for this selection because I am getting the error like ( Lower limit is greater than uppre limit ).

Thanks ,

Vinay.

1 ACCEPTED SOLUTION

gastn_jareo
Active Participant
0 Kudos

I think you should not use a simple SELECT-OPTION since it allows you to include and exclude a lot of combinations of ranges and particular days and hours, and is not clear what you should select for all cases. If you use SELECT-OPTION with NO-EXTENSION clause it will be more clear and may be it will cover what you need. Then, you may use it:


SELECT-OPTIONS: udat1 ...  NO-EXTENSION,
                utime1...  NO-EXTENSION.

SELECT objectclas objectid FROM cdhdr INTO CORRESPONDING FIELDS OF TABLE mt_cdhdr
WHERE objectclas eq 'STUE_V'
AND ( ( udate > udat1-low AND udate < udat1-high ) OR
      ( udate = udat1-low AND utime >= utime1-low ) OR
      ( udate = udat1-high AND utime <= utime1-high) ).

I think this solution is like others above. The main difference is the use of NO-EXTENSION clause.

Hope it helps!

16 REPLIES 16

FredericGirod
Active Contributor
0 Kudos

select-options : s_date for ... no-extension ,

s_time for .. no-extension

( date eq s_date-low and

time ge s_time-low ) or

( date eq s_date-high and

time le s_time-high ) or

(date gt s_date-low and

date lt s_date-high )

former_member196079
Active Contributor
0 Kudos

Hi Vinay

Define your select option without extensione


s_time for date no-extension

and change the select as follow:


SELECT objectclas objectid FROM cdhdr INTO CORRESPONDING FIELDS OF TABLE mt_cdhdr
WHERE objectclas eq 'STUE_V'
AND udate in udat1
AND (utime ge utime1-low
OR utime le utime1-high).

Best regards

Marco

vinod_vemuru2
Active Contributor
0 Kudos

Hi,

Your lower limit time is greater than upperlimit time. You can do it in 2 ways.

1. Just pass only date and filter it later by date and time combination.

2. Pass the combination of date and time to where clause.(May lead to performance issues)

eg: WHERE objectclas eq 'STUE_V'

AND (( UDATE GE 04.05.2010 AND UTIME GE 23:24:21 ) OR ( UDATE LE 05.05.2010 AND UTIME LE 06:24:33 )).

Use appropriate syntax and variables.

Thanks,

Vinod.

SuhaSaha
Advisor
Advisor
0 Kudos

Hello,

This is because in the field UTIME1 the low value is greater than the high value.

As a user you are trying to select data from 04.05.2010 / 23:24:21 to 05.05.2010 / 06:24:33 but unfortunately the compiler does not have sufficient AI to interpret this.

It takes the date & time fields separately and finds for the UTIME field you have an erroneous input.

No how to handle this situation i think the other users have already given you sufficient hints.

BR,

Suhas

PS: For these specific cases we have time-stamp field.

Former Member
0 Kudos

Hi All,

Its working fine now for below condition.

UDATE 04.05.2010 ( From ) UDATE 05.05.2010 ( TO )

UTIME 23:24:21 ( From ) UTIME 06:24:33 ( TO ).

But getting all the values of above condition for this below condition.

UDATE 05.05.2010 ( From ) UDATE 05.05.2010 ( TO )

UTIME 06:24:33 ( From ) UTIME 07:24:33 ( TO ).

Please check and let me know.

Thanks.

Vinay

Edited by: Vinay parakala on May 5, 2010 12:34 PM

0 Kudos

my solution works also with that

Former Member
0 Kudos

Not yet solved

Former Member
0 Kudos

hi

your query should be rewritten as ,

SELECT objectclas objectid FROM cdhdr INTO CORRESPONDING FIELDS OF TABLE mt_cdhdr

WHERE objectclas eq 'STUE_V'

AND udate ge udat1-low

and udate le udat1-high

AND utime ge utime1-low

and utime le utime1-high.

Thanks

Seema

0 Kudos

You could write your code like this:

SELECT objectclas objectid FROM cdhdr INTO CORRESPONDING FIELDS OF TABLE mt_cdhdr
WHERE objectclas eq 'STUE_V'
AND (( udate = udat1-low AND utime >= utime1-low) 
OR (udate > udat1-low and udate < udat1-high)
OR (udate = udat1-high and utime <= utime1-high)). 

0 Kudos

Hi Gusthavo

Thanks for message . But your code was like above ....Not yet solved.

Vinay.

gastn_jareo
Active Participant
0 Kudos

I think you should not use a simple SELECT-OPTION since it allows you to include and exclude a lot of combinations of ranges and particular days and hours, and is not clear what you should select for all cases. If you use SELECT-OPTION with NO-EXTENSION clause it will be more clear and may be it will cover what you need. Then, you may use it:


SELECT-OPTIONS: udat1 ...  NO-EXTENSION,
                utime1...  NO-EXTENSION.

SELECT objectclas objectid FROM cdhdr INTO CORRESPONDING FIELDS OF TABLE mt_cdhdr
WHERE objectclas eq 'STUE_V'
AND ( ( udate > udat1-low AND udate < udat1-high ) OR
      ( udate = udat1-low AND utime >= utime1-low ) OR
      ( udate = udat1-high AND utime <= utime1-high) ).

I think this solution is like others above. The main difference is the use of NO-EXTENSION clause.

Hope it helps!

0 Kudos

Sorry! I found an error in my solution in case you want to select only one day and then udat1-low = udat1-high. The corrected solution could be:

 
SELECT-OPTIONS: udat1 ...  NO-EXTENSION,
                utime1...  NO-EXTENSION.
 
SELECT objectclas objectid FROM cdhdr INTO CORRESPONDING FIELDS OF TABLE mt_cdhdr
WHERE objectclas eq 'STUE_V'
AND ( ( udate > udat1-low  AND udate < udat1-high ) OR
      ( udate = udat1-low  AND udate < udat1-high AND utime >= utime1-low ) OR
      ( udate = udat1-high AND udate > udat1-low  AND utime <= utime1-high) OR
      ( udate = udat1-low  AND udate = udat1-high AND utime >= utime1-low AND utime <= utime1-high ) ).

Regards!

0 Kudos

Hi All,

I am not using any select options so there is no matter of extensions here.

You can see my code as below. last run date and time I am getting from ztable

SELECT SINGLE ZDATE ZTIME INTO (V_DATE,V_TIME ) FROM ZWHVBOM.

if sy-subrc eq 0.

Udat1-sign = 'I'.

Udat1-option = 'BT'.

Udat1-low = v_date.

Udat1-high = sy-datum.

V_DATE1 = Udat1-high.

Append udat1.

Utime1-sign = 'I'.

Utime1-option = 'BT'.

Utime1-low = v_time.

Utime1-high = sy-uzeit.

V_TIME1 = Utime1-high.

Append utime1.

endif.

V_TIME1 = Utime1-high.

SELECT objectclas objectid FROM cdhdr INTO CORRESPONDING FIELDS OF TABLE lt_cdhdr

WHERE objectclas eq 'STUE_V'

AND udate in udat1

AND utime in utime1

AND ( tcode eq 'CS01'

OR tcode eq 'CS02'

OR tcode eq 'C201'

OR tcode eq 'C202' ).

Thanks - Vinay

0 Kudos

Ok. Then you may use this:

 
SELECT SINGLE ZDATE ZTIME INTO (V_DATE,V_TIME ) FROM ZWHVBOM.
if sy-subrc eq 0.
  Udat1-low = v_date.
  Udat1-high = sy-datum.
  Utime1-low = v_time.
  Utime1-high = sy-uzeit.
endif.

SELECT objectclas objectid FROM cdhdr INTO CORRESPONDING FIELDS OF TABLE mt_cdhdr
WHERE objectclas eq 'STUE_V'
AND ( ( udate > udat1-low  AND udate < udat1-high ) OR
      ( udate = udat1-low  AND udate < udat1-high AND utime >= utime1-low ) OR
      ( udate = udat1-high AND udate > udat1-low  AND utime <= utime1-high) OR
      ( udate = udat1-low  AND udate = udat1-high AND utime >= utime1-low AND utime <= utime1-high ) )
AND ( tcode eq 'CS01' OR
      tcode eq 'CS02' OR
      tcode eq 'C201' OR
      tcode eq 'C202' ).

I deleted V_DATE1 and V_TIME1 variables since they are not used on the select. I don't know if you'll need them later for other purposes.

Regards!

Edited by: Gastón Jareño on May 5, 2010 10:49 AM

Actually the ENDIF should be after the SELECT, preventing the case the first SELECT is empty.

0 Kudos

This doesn't work if udat1-low and udat1-high because it would select all the entries for that date regardless of time.

Former Member
0 Kudos

I guess the simpler solution then is not to use ranges. If you don't use ranges or select-options and use 4 parameters or variables

From date

to date

from time

to time

and then use one of the selects proposed here, it will work.