# Thread: Time ranges

1. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421

## Unanswered: Time ranges

I have some ideas of my own but would like to hear your opinion,
I need to create some time ranges per weekday,
Monday, 00:00, 04:00
Monday, 04:00, 09:00
And so on, to...
Monday, 21:00, 24:00
And this needs to be different for each weekday...
Anyway, problem might now be obvious... I (my users) need only to enter 24:00 (the actual time) however 24:00 does not excist, its 00:00. That in it self is explainable. But this poses a problem becuase 21:00 > 00:00, so if i am looking for 22:00 and do a query:

Where StartTime <=#22:00:00# and EndTime >=#22:00:00# I dont get any match, how did you solve this?

There are 2 posible solutions to this (i think)

1) Manipulate the input...
Did you change the 00:00 in the time range to 31-dec-1899 00:00. If you enter only time the date defaults to 30-dec-1899, so the 21:00 (or any search time) would be 30-dec-1899. thus above search would return the proper range...

2) Manipulate the query, and consequent calculations....
I could change the query to:
Where StartTime <=#22:00:00# and (EndTime >=#22:00:00# or EndTime = #00:00:00#)
This also returns the proper range...

However the next step (offcourse) is i need to calculate using these ranges:
1) datediff("H",#22:00:00#,#31-dec-1899 00:00:00#)
2 hours

2) datediff("H",#22:00:00#,#00:00:00#)
-22 hours

So in solution 2 i would need to add something like:
If ... < 0 then ... = 24 - 22 hours
to get the 2 i need and proceed....

I am leaning to solution 1 at the moment, it seems to be less involved. But what do you folks think? Or even do you have another possibly better way?

Regards

2. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
Isnt anybody going to offer his/her opinion on this? Or did i not make myself clear enough?

Regards

3. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
Doesnt anybody have any thoughts on this?

Greetzzzzzzzzzzzzzz

4. Registered User
Join Date
Jun 2004
Posts
96
namliam,
my view would be to get the dates and time for both and do a comparison (eg: datediff). You can always default the dates to be the current day if you don't want additional work for the users. But at least this is much clearer to the users also.

just my views.

Cyherus

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•