Combined Date & Time Filter

[ChrisHawkes]

I need to select records based on 2 items.

1. Date (Field in table)
2. Time (Field in table)

The problem I have is that for any 1 day (date) the time range is 09:00 to 02:00. So, if I enter the date (for report) of say 31/03/2001, I want it to include the records which satisfy the time criteria as well i.e 09:00 on 31/3/2001 to 02:00 on 01/04/2001.

So far I have come up with the following which works for 1 day:

(Call_date=DATE GIVEN .and. toseconds(right(alltrim(call_time),8))>= 32400).OR.
(Call_date=DATE GIVEN+1 .and. toseconds(right(alltrim(call_time),8))

[CraigSchumacker]

RE: Combined Date & Time Filter



Chris:

Maybe I'm just mis-reading this, but it seems like you already have most of the answer.
I not sure I get the time part of the question. If a day is (always)
between 9:00 AM and 2:00 PM, why even bother with the times, unless you are
asking the user to enter a time, like 12:00 PM on 03/31/2001 -" 01:00 PM on
04/02/2001?
Anyhow, you need to aquire two dates from the user, or offer a dialog box
that, on a button push, say "THIS MONTH" calculates the first day of
the month (based on the system date) and the last day of the month, and sets
those dates as variables. SEE BELOW FOR A SAMPLE DIALOG BOX POSTED IN THE
CODE ARCHIVE!
Below is a expression that will give you 24 hour blocks, although the 32400
and 50400 needs to be changed to a variable if you want misc times for the
reports. If not using misc. times, and using solid days, you can drop the
time part of the expression.
I included the RIGHT and ALLTRIM, although I'm not sure why they are
there. It seems like the fields actual characters should be sized exactly
to the time footprint you are using, but maybe your drawing off a time card run,
or the like...

( (FIELD_CALL_DATE
"= VAR_BEGIN_DATE) .and. (toseconds(right(alltrim(FIELD_CALL_TIME),8))"=
32400) )
.AND.
( (FIELD_CALL_DATE
"= VAR_END_DATE) .and. (toseconds(right(alltrim(FIELD_CALL_TIME),8))"=
50400) )
If your using this in some operation outside the
report, you must use global variables.
Look in the code archive for a recent post that
illustrates a dialog box that offers the user a button push selection of months,
weeks, days, etc... if you want to, and change the formula above to evaluate the
correct time parameters based on the system date. Most of the formula is
already worked out for you in Terry's example. If other than a button push
selection is desired, a begin date and an end date are still needed from the
user.
Hope this is what you were looking for... If not,
I appologize. Sometimes I have trouble reading between the lines!!!
Craig

[CraigSchumacker]

RE: Combined Date & Time Filter

Chris:

The recent post in the archive is labeled "setting dates", and includes a downloadable sample.

Craig

[ChrisHawkes]

RE: Combined Date & Time Filter

Thanks Craig, I'll try to use your suggestion. The point I was trying to make is that for this particular client the accounting day is split into 2 shifts; 09:00 to 18:00 and 18:00 to 02:00. The reporting needs the time as the for obvious reasons an accounting day can be 2 dates. For example, 31/03/2001 is, in fact 31/03/2001 at 09:00 to midnight, AND 01/04/2001 at midnight to 02:00.

[Tom Cone Jr]

RE: Combined Date & Time Filter

Chris,

what's the format you are using for the time values?

I'm thinking that if you were to use a twenty-four hour format (military time), your filter could include alternatives, depending on whether the time is greater than 1200 hours or not.

i.e.

if timevalue

[ChrisHawkes]

RE: Combined Date & Time Filter

Thanks Tom,

I am already using the 24 hour format.

Chris

[Tom Cone Jr]

RE: Combined Date & Time Filter

Chris, sorry about the previous post, I misunderstood your question.

the between() function can be used in a filter expression to limit the output to a specified range of dates. check the examples in the xbasic ref. manual.

[ChrisHawkes]

RE: Combined Date & Time Filter

Tom

How would I use BETWEEN in my scenario:

e.g. I wish to find all the records that satisfy the date range 01/03/2001 to 31/03/2001. Bearing in mind that the times for these records will mean a further date of 1/4/2001.

Each DAY is comprised of 17 hours from 09:00.

So, if I use 31/3/2001, I will get those records that have a date of 31/03/2001 AND time>= 09:00 AND those records that have a date 01/04/2001 AND time

[forskare]

RE: Combined Date & Time Filter

Chris,

There was a post not too long ago in reference to searching for time that continued form one day to the next. A solution was found but I don't recall what it was or the subject. Perhaps others might recall the subject of the post. I suspect your answer is there.

kenn

[ChrisHawkes]

RE: Combined Date & Time Filter

Thanks Ken,

I think I found the posts you are referring to, but unfortunately they don't apply to my situation. I am really stuck on this problem, and I don't seem to be able to get around it. Any further suggestions would be most welcome in addition to those already here.

Chris

[Finian Lennon]

RE: Combined Date & Time Filter

Add a field to your table - I call ours Query and in field rules define it as a calculated field. The calc definition is

CDATE(APPT_DATE)+PADL(LTRIM(TRIM(STR(TOSECONDS(APPT_TIME)))),6,"0")

this creates a character string that combines the date and the seconds.

To query for a range you need to convert your search string into a like format. So to search for records between January 1 and March 31 (assuming that the users don't need to think about the following day requirement) your code would take the second input date and add 1; so your code would search for dates between 01/01/2001 and greater than 9:00 am and 04/01/2001 and less than or equal to 2:00 am.

This would translate into a query filter of

Between(query,200101032400,20010401007200)

and, if the table is indexed on the "Query" field. will be instantaneous.

Finian

[Finian Lennon]

RE: Combined Date & Time Filter

PS the Query field needs to be defined as C, 14 and in the example above the time field is called Appt_Time

Finian

[Finian Lennon]

RE: Combined Date & Time Filter

Geez, I'll get this right sometime ...
a correct sample filter would be

Between(query,"200101032400","20010401007200")

F.

[ChrisHawkes]

RE: Combined Date & Time Filter

Finian

Thanks very much - It hit the nail on the head, such a beautiful way of doing it. I wonder how long it took you to think of this solution?

Chris

[Finian Lennon]

RE: Combined Date & Time Filter

>

No time at all, since the calculated field was lifted directly from our appointment scheduler which is built on queries based on that expression. And while I'd like to credit inspiration or perspiration for the original idea/guideline, I can't since it was generously given to me by my friend and mentor Peter Wayne.

Finian