topic Re: date and time problem for selection. in Application Development and Automation Discussions

date and time problem for selection.

Former Member — Wed, 05 May 2010 09:28:56 GMT

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.

Re: date and time problem for selection.

FredericGirod — Wed, 05 May 2010 09:34:59 GMT

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 )

Re: date and time problem for selection.

Former Member — Wed, 05 May 2010 09:39:41 GMT

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

Re: date and time problem for selection.

vinod_vemuru2 — Wed, 05 May 2010 09:39:57 GMT

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.

Re: date and time problem for selection.

SuhaSaha — Wed, 05 May 2010 09:41:54 GMT

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.

Re: date and time problem for selection.

Former Member — Wed, 05 May 2010 10:34:33 GMT

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

Re: date and time problem for selection.

FredericGirod — Wed, 05 May 2010 11:54:29 GMT

my solution works also with that

Re: date and time problem for selection.

Former Member — Wed, 05 May 2010 13:37:25 GMT

Not yet solved

Re: date and time problem for selection.

Former Member — Wed, 05 May 2010 13:47:07 GMT

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

Re: date and time problem for selection.

Former Member — Wed, 05 May 2010 13:57:14 GMT

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

Re: date and time problem for selection.

Former Member — Wed, 05 May 2010 14:17:49 GMT

Hi Gusthavo

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

Vinay.

Re: date and time problem for selection.

gastn_jareo — Wed, 05 May 2010 14:26:00 GMT

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!

Re: date and time problem for selection.

gastn_jareo — Wed, 05 May 2010 14:34:43 GMT

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!

Re: date and time problem for selection.

Former Member — Wed, 05 May 2010 14:36:30 GMT

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

Re: date and time problem for selection.

gastn_jareo — Wed, 05 May 2010 14:47:09 GMT

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.

Re: date and time problem for selection.

Former Member — Wed, 05 May 2010 14:58:15 GMT

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.

Re: date and time problem for selection.

thelazyhangman — Thu, 22 Jun 2023 12:07:30 GMT

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