Date and Time problem

[Former Member]

I have this query where you have to get all the data from a certain date and ranges of time. For example.

From:

Date: 01.01.06 - 01.02.06

Time ranges: 07:00:00 - 23:59:59

00:00:00 - 07:00:00

The problem is that I'm getting the data of 00:00:00-07:00:00 for the first date and data from 07:00:00-23:59:59 of the second date.

I just want to query the data from 7am to 7am of the following date.

Thanks for any help you may give.

[suresh_datti · Accepted solution]

Hi Adrian,

the Time range should be specific to each date...

FRDAT FRTIME TOTIME

TODAT FRTIME TOTIME

Regards,

Suresh Datti

[Former Member]

How about if you're going to query from 01.01.06 - 01.10.06? Do I need to put every single time ranges for each date?

The only problem I have is that I'm getting the data from the first and last date.

For the first date, I'm getting the data from 00:00:00-07:00:00

For the last date, I'm getting the date from 07:00:00-23:59:59

Is there a simple approach?

Or is there a way to change, in ABAP, the time range of a day. The time from 00:00:00 to 23:59:59 is equal to one day. Is is possible to do it like 07:00:00 of the current date to 07:00:00 of the next day? This may sound so weird but if there is, that may sound better.

[suresh_datti]

HI,

I repeat, the time range has to be specified for each date you want to query on.. I don't think there is a simpler approach in ABAP.. will be glad to hear from others..

Regards,

Suresh Datti

[Former Member]

Hi

If you want to do by only one select, try a code like this:

SELECT * FROM TABLE WHERE ( DATE = DATE-LOW AND

TIME >= 7 AND TIME <= 235959)

OR ( DATE = DATE-HIGH AND

TIME <= 7).

or you can split your select in two select:

SELECT * FROM TABLE WHERE DATE = DATE-LOW AND

( TIME >= 7 AND TIME <= 235959 ).

SELECT * FROM TABLE WHERE DATE = DATE-HIGH AND

TIME <= 7

Max

[RichHeilman]

I had this same problem a while back, you have to handle it special. What I did is created a from and to range for the TIME, but not a select-option, I built it manually. Also, I am limiting you to put only a range in the date select-option. Then you need to build up a timestamp range which includes the date and time.

Once that is built you can select you data from the database based on the date range of the select-option, then loop thru the internal table and check the date/time fields of the internal table against the timestamp range. Here is some of the code, i didn't put all, because it is a big program. Hopefully you get the idea.

* Miscellanous Data Declarations
DATA: FIELDCAT TYPE SLIS_T_FIELDCAT_ALV.
DATA: RESTRICT TYPE SSCR_RESTRICT.
DATA: TIME(8) TYPE C.
DATA: TIMESTAMP(14) TYPE C.
RANGES: R_TIMESTAMP FOR TIMESTAMP.

SELECTION-SCREEN BEGIN OF BLOCK B2 WITH FRAME TITLE TEXT-002 .
SELECT-OPTIONS: S_PERSCN FOR ZSHIPMENT_EXCEPT-PERSCN.
SELECT-OPTIONS: S_SCUSER FOR ZSHIPMENT_EXCEPT-SCUSER.
SELECT-OPTIONS: S_SCDATE FOR ZSHIPMENT_EXCEPT-SCDATE.
<b>SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN COMMENT 1(20) TEXT-003 FOR FIELD P_SCTMST.
SELECTION-SCREEN POSITION 33.
PARAMETERS:     P_SCTMST TYPE ZSHIPMENT_EXCEPT-SCTIME.
SELECTION-SCREEN COMMENT 52(5) TEXT-004 FOR FIELD P_SCTMED.
PARAMETERS:     P_SCTMED TYPE ZSHIPMENT_EXCEPT-SCTIME.
SELECTION-SCREEN END OF LINE.</b>
SELECTION-SCREEN END OF BLOCK B2.

INITIALIZATION.
  PERFORM INITILIZATION.

<b>AT SELECTION-SCREEN.
  PERFORM DATE_TIME_CHECKS.

START-OF-SELECTION.

  PERFORM BUILD_TIME_STAMP.</b>
  PERFORM GET_DATA.
  PERFORM CALL_ALV.

************************************************************************
*  INITILIZATION
************************************************************************
FORM INITILIZATION.

* Clear internal tables
  CLEAR IZSHIPWORK.   REFRESH IZSHIPWORK.
  CLEAR IZSHIPEXCEPT. REFRESH IZSHIPEXCEPT.
  CLEAR IZSHIPMENT.   REFRESH IZSHIPMENT.
  CLEAR R_TIMESTAMP.  REFRESH R_TIMESTAMP.

* Restrict the select options for S_SCDATE
* to just a date range
  DATA: SELOPT   TYPE SSCR_ASS.
  DATA: OPT_LIST TYPE SSCR_OPT_LIST.

  CLEAR OPT_LIST.
  OPT_LIST-NAME       = 'BT'.
  OPT_LIST-OPTIONS-BT = 'X'.
  APPEND OPT_LIST TO RESTRICT-OPT_LIST_TAB.
  SELOPT-KIND            = 'S'.
  SELOPT-NAME            = 'S_SCDATE'.
  SELOPT-SG_MAIN         = 'I'.
  SELOPT-SG_ADDY         = ' '.
  SELOPT-OP_MAIN         = 'BT'.
  SELOPT-OP_ADDY         = 'BT'.
  APPEND SELOPT  TO RESTRICT-ASS_TAB.

  CALL FUNCTION 'SELECT_OPTIONS_RESTRICT'
       EXPORTING
            RESTRICTION            = RESTRICT
       EXCEPTIONS
            TOO_LATE               = 1
            REPEATED               = 2
            SELOPT_WITHOUT_OPTIONS = 5
            SELOPT_WITHOUT_SIGNS   = 6
            INVALID_SIGN           = 7
            EMPTY_OPTION_LIST      = 9
            INVALID_KIND           = 10
            REPEATED_KIND_A        = 11
            OTHERS                 = 12.


ENDFORM.

************************************************************************
*  DATE_TIME_CHECKS
************************************************************************
FORM DATE_TIME_CHECKS.

* Date and Time has to be handled special
* due to user crossing over dates

* Force values for both low and high
  IF S_SCDATE-LOW IS INITIAL
     AND NOT S_SCDATE-HIGH IS INITIAL.
    MESSAGE E054.
  ENDIF.

  IF NOT S_SCDATE-LOW IS INITIAL
     AND S_SCDATE-HIGH IS INITIAL.
    MESSAGE E054.
  ENDIF.

  IF NOT P_SCTMST IS INITIAL
     AND P_SCTMED IS INITIAL.
    MESSAGE E054.
  ENDIF.

  IF P_SCTMST IS INITIAL
     AND NOT P_SCTMED IS INITIAL.
    MESSAGE E054.
  ENDIF.

* If date-low and date-high are the same
* make sure that time range is logical
  IF S_SCDATE-LOW = S_SCDATE-HIGH
    AND    P_SCTMST > P_SCTMED.
    MESSAGE E054.
  ENDIF.

* if time range fields have been cleared
* then make them initial.
  IF P_SCTMST = SPACE.
    P_SCTMST = '000000'.
  ENDIF.

  IF P_SCTMED = SPACE.
    P_SCTMED = '000000'.
  ENDIF.

ENDFORM.

<b>************************************************************************
*  BUILD_TIME_STAMP
************************************************************************
FORM BUILD_TIME_STAMP.

* Build the TIMESTAMP with the date/time ranges

  CLEAR R_TIMESTAMP. REFRESH R_TIMESTAMP.

  IF NOT P_SCTMST IS INITIAL
     AND NOT P_SCTMED IS INITIAL.

    R_TIMESTAMP-SIGN = 'I'.
    R_TIMESTAMP-OPTION = 'BT'.
    R_TIMESTAMP-LOW+0(8) =  S_SCDATE-LOW.
    R_TIMESTAMP-LOW+8(6) =  P_SCTMST.
    R_TIMESTAMP-HIGH+0(8) =  S_SCDATE-HIGH.
    R_TIMESTAMP-HIGH+8(6) =  P_SCTMED.
    APPEND R_TIMESTAMP.
  ENDIF.

ENDFORM.</b>

FORM GET_DATA.


  DATA: ZSHIP_TS(14) TYPE C.

* Move work table to combined table
  LOOP AT IZSHIPWORK.
    CLEAR: IZSHIPMENT, ZSHIP_TS.

<b>* Filter on Time range
    IF NOT P_SCTMST IS INITIAL
      AND NOT P_SCTMED IS INITIAL.
      ZSHIP_TS+0(8) = IZSHIPWORK-SCDATE.
      ZSHIP_TS+8(6) = IZSHIPWORK-SCTIME.
      IF NOT ZSHIP_TS IN R_TIMESTAMP.
        CONTINUE.
      ENDIF.
    ENDIF.</b>

endif.

ENDFORM.

Regards,

Rich Heilman