MVSFORUMS.com

[Jose]

Could anyone help me in writing a query for the below requirements?

I have a DB2 table with columns:
D_ACT defined as DATE (format CCYY-MM-DD)
T_ACT defined as TIME (format HH.MM.SS)

I want to retrieve all the rows of the DB2 table that meets the input select criteria of:
START-DATE & START-TIME (formats CCYY-MM-DD & HH.MM.SS)
END-DATE & END-TIME (same as above)

Thanks.

[Dibakar]

Jose,

I didn't understand what you want to do, can you give example?

Diba.

[CraigG]

Without more details as to what your selection criteria would look like it is hard to offer any help. A timestamp column would make it simpler but with the date and time separated it can result in complex where statements.

To select the rows with a date & time greater than or equal to 2008-10-01 at 11:00:00 and a date & time less than of equal to 2008-10-03 at 13:00:00

[danm]

Try

[kolusu]

Danm sql is an excellent choice to get the desired results between 2 date time ranges

Kolusu

[bauer]

Well, i agree with kolusu.

But i'm not sure: Is an index on the date and time column used for this select?

For performance critical applications the soloution of CraigG may be better.

regards
bauer

[Jose]

danm, your query is much simpler than mine. Thanks to you and to all who provided solutions & suggestions.
I tested my own query afew times and it looks like it is working (not 100% sure) but yours is so much simpler. My query looks like this:

SELECT * FROM TABLE
WHERE
(D_ACT = :START-DATE
AND T_ACT >= :START-TIME
AND
( (D_ACT = :END-DATE
AND T_ACT <= :END-TIME)
OR (D_ACT < :END-DATE) )
)
OR
( D_ACT > :START-DATE
AND D_ACT = :END-DATE
AND T_ACT <= :END-TIME)
OR
( D_ACT > :START-DATE
AND D_ACT < :END-DATE)

[Jose]

bauer, The exisiting DB2 table is not indexed by these columns, D_ACT & T_ACT. That's one of my concerns for performance issue. I will try CraigG's suggestion as well. Thanks.

[haatvedt]

Jose,

this would be much simpler if your table had a TIMESTAMP column instead of a DATE and a TIME column.

if you use separate columns you may want to consider using the following... do EXPLAINS on both versions to check the access path.

the reason for the separate criteria on the D_ACT is to cause DB2 to use an index which has the D_ACT as the first column. I would suggest adding T_ACT as the second column on the index.

SELECT * FROM TABLE
WHERE D_ACT BETWEEN :START-DATE AND :END-DATE
AND
(D_ACT = :START-DATE
AND T_ACT >= :START-TIME
AND
( (D_ACT = :END-DATE
AND T_ACT <= :END-TIME)
OR (D_ACT < :END-DATE) )
)
OR
( D_ACT > :START-DATE
AND D_ACT = :END-DATE
AND T_ACT <= :END-TIME)
OR
( D_ACT > :START-DATE
AND D_ACT < :END-DATE)
_________________
Chuck Haatvedt

email --> clastnameatcharterdotnet

(replace lastname, at, dot with appropriate
characters)