Oracle pull records with specific date and time

[mhansler]

I need to devise three CR9 queries to pull data from 100000's of records. We've simplied it to pull at three specific times during the day. 09:00:00AM, 14:00:00PM and 16:00:00 PM. It's an oracle database with both date and time probes in the report. I've used datevalue, datetime etc. to no avail.

A sample is ..

{RUN_DATE} = DateTime (2004, 05, 01, 09, 00, 00) and RUN_DATE} = DateTime (2004, 05, 01, 14, 00, 00) and RUN_DATE} = DateTime (2004, 05, 01, 16, 00, 00)

This select gets no errors, but when run I no records and it's done in 1 second. These queries take a long time tor process. I know there are records. Can someone help me to see what I am doing wrong. Thank you.

 

[synapsevampire]

It's very unlikely that your timestamps are precisely 9, 14 and 16, which explains the lack of rows returned.

Perhaps what you really want is to pull rows for a given day broken up into three different groups using the hours described above.

The record selection formula would have something like:

Try:

{RUN_DATE} >= DateTime (2004, 05, 01, 0, 0, 0) and {RUN_DATE} <= DateTime (2004, 05, 01, 16, 00, 00)

This would pull everything from midnight to 4PM. Then you can create a formula to group them by.

If you want just the 3 hour groups, try:

(
{RUN_DATE} >= DateTime (2004, 05, 01, 9, 0, 0) and {RUN_DATE} <= DateTime (2004, 05, 01, 9, 59, 59)
)
or
(
{RUN_DATE} >= DateTime (2004, 05, 01, 14, 0, 0) and {RUN_DATE} <= DateTime (2004, 05, 01, 14, 59, 59)
)
or
(
{RUN_DATE} >= DateTime (2004, 05, 01, 16, 0, 0) and {RUN_DATE} <= DateTime (2004, 05, 01, 16, 59, 59)
)


Hope this helps. If not, rather than explaining how you intend to satisfy requirements, describe the environment and the actual requirements, such as:

Crystal version
Database/connectivity used
Example data
Expected output

-k