Extracting data between time range (1 Viewer)

[gilescis]

I have a report that I need to pull records for time range.
I am using Crystal XI

incident.date = date&time dd/mm/yy hh:mm:ss

incident.date holds both the date and timestamp of the record.

I need the formula to look in the incident.date field and then look at the time stamp in 24hr time and if the time is between 17:00:00 and 07:00:00 then return records.

I have tried to use the cTime and Istime but no success

Any help greatly appreciated

Castaldo
Clyde FD

 

[Kempes]

I can cetainly help you with this but based on my Crystal version 10.

There are few ways of doing this. Firstly,

Are the dates to report on prompted, or would you like them to be prompted?
Are the dates static, Ie, always report on this date and time forever? (probably not likely)
Do they have a regular pattern, ie, last full week, or from yesterday to today etc?

If you let me know, I can tell you the solution.

 

[gilescis]

I do need to pull the date range so yes I would like to make it prompt just for the date range.

No the dates could change but the time wont. This report will always be for records entered between 1700hrs and 0700hrs

No irregular patterns

 

[Kempes]

Try this,

add a parameter field and call it from and change the type to date.
Do the same again but call it to.

Now in your selection criteria editor, adapt the following.

{Yourdatetimefield} in DateTime ({?From},time(17,00,00)) to DateTime ({?To},time(07,00,00))

This will now prompt for date, yet fix the time as shown.

Hope this helps

 

[gilescis]

I have tried this {in5basic.ALARMDATE}in DateTime ({?from},time(17,00,00)) to DateTime ({?to},time(23,59,59)) and it does 2 things.

1. it returns some duplicate records. anotherword it returns incident # 2008025 twice, but not all records are dups.

2. It pulls in records from 02:00 etc. I only want from 1700 - 2359

Any Ideas

 

[Kempes]

Out of interest, what dates did you put in to return 02:00?

 

[gilescis]

begdate - 01/01/08
ending date 08/31/08

then i want it to return all the records that are from 5pm - 7am the next day

 

[Kempes]

Ah, in that case you need to seperate out the time as a stand along object.
Create a new formula field, and enter the following

Time(yourdatetimefield)

Add this formula field to your selection criteria as between time and time.

Now, change the date to between {?from} to {?to}

This should do it but will need testing as I haven't tested it.

You might need to seperate out date into a formula field too if this doesn't work.

Let me know if you need any more help with this.

 

[gilescis]

{@time2} in Time (17, 00, 00) to Time (7, 00, 00) and
{in5basic.ALARMDATE} in {?from} to {?to}

Still returns records for 8am 9am 10am etc

 

[gilescis]

Any Ideas on this last post i did

Thanks