Select Records between Begin Date/Time and End Date/Time

[Former Member]

Hi,

I need to select records from table REGUT where the TSDAT and TSTIM are between a START DATE/TIME and END DATE/TIME.

START DATE 20091021

START TIME 135044

END DATE 20091022

END TIME 125736

How to select data from table with respect to date and time field of a table.

Date field - TSDAT and Time field - TSTIM.

Here Start time is greater than End Time, Please help me with the logic.

Regards,

Ashok

[former_member191735]

> I need to select records from table REGUT where the TSDAT and TSTIM are between a START DATE/TIME and END DATE/TIME.

>

>

> START DATE 20091021

> START TIME 135044

> END DATE 20091022

> END TIME 125736

>

> How to select data from table with respect to date and time field of a table.

> Date field - TSDAT and Time field - TSTIM.

>

> Here Start time is greater than End Time, Please help me with the logic.

This thread may get locked but anyway here is my input for this

You can use it but when you select you need to use AND and OR in where clause

Try this

where (start date GT 'somedate' AND start time GT 'some time') and

(End date LT 'somedate' AND end time LT 'some time' ).

This should solve your problem

Good luck

[Former Member]

Hi Sampath,

Here Start time is greater than End time , so select is not fetching the correct data.

Ashok

[former_member191735]

Date field - TSDAT and Time field - TSTIM.

where ( ( TSDAT GT 'somedate' and TSTIM GT 'Some time' ) and

(TSDAT LT 'Some date' and TSTIM LT 'Sometime' ) )

That is the reason we have ( ) and i have included start date and start time and then end time and end date.

If you are using the start date, time , end date and time in your selection screen of the report .. . You have to make them as paramters like

Parameters: p_startdate , p_start time, p_enddate, p_end time.

I assume, you have select-options for date and time and you want to use the low as start and high as end but this doesn't work on selection screen itself.

If that doesnt work... let me know and if it is not large data, go for selecting maximum and loop through the data. I mean go for dates and loop for time as anyways this select statement will be a performance issue

Edited by: Sampath Kumar on Nov 17, 2009 9:10 AM

[Former Member]

The logic to do this type of selection is fairly simple. It breaks down into 3 distinct groups of data that you want to select as follows:

1. Data where the date is equal to the start date, and the time is greater than or equat to the start time.

2. Data where the date is greater than the start date and less than the end date

3. Data where the date is equal to the end date , and the time is less than or equal to the end time.

so code:

WHERE 
   ( ( TSDAT = start_date AND TSTIM >= start_time ) OR
     ( TSDAT > start_date AND TSDAT < end_date ) OR
     ( TSDAT = end_date AND TSTIM <= end_time ) ).

Note, with a mix of AND and OR like this, the select may be a bit slow depending on lots of database factors

Andrew

[thelazyhangman]

Hello,

this doesn't work if start_date and end_date are the same, because it would select all the entries for that day regardless of the time.

[Former Member]

Hi Ashy,

Try this:

WHERE ( ( TSDAT = STARTDATE AND TSTIM >= STARTTIME ) OR TSDAT > STARTDATE )

AND ( ( TSDAT = ENDDATE AND TSTIM <= ENDTIME ) OR TSDAT < ENDDATE ).

Regards,

Richard

[venkat_o]

Hi Ashy, Try this way.

REPORT ztest_notepad.
DATA:it_regut TYPE STANDARD TABLE OF regut WITH HEADER LINE.
SELECT-OPTIONS:date FOR it_regut-tsdat,
               time FOR it_regut-tstim.
"START-OF-SELECTION.
START-OF-SELECTION.
  SELECT * FROM  regut INTO TABLE it_regut
         WHERE  tsdat  BETWEEN date-low AND date-high
         AND    tstim  BETWEEN time-low AND time-low.

Thanks Venkat.O

[Former Member]

Hi

If you are using a SELECT-OPTION to capture the user's input then the SELECT query would be like:

SELECT           <field1>,<field2>,......
FROM             regut
INTO TABLE  itab
WHERE          tsdat BETWEEN so_tsdat-low AND so_tsdat-high
AND               tstim BETWEEN so_tstim-low AND so_tstim-high

And if you are making use of parameters, then either create ranges for the date and time and proceed with BETWEEN addition in WHERE clause or use AND in WHERE clause for pa_sdate and pa_edate and similarly for time also.

Regards

Gaurav

[Former Member]

This message was moderated.