Searching for record with date and time

[dleggett]

Hi

I am trying to Find records which are greater than the current Date and Time. I have 2 fields called EndDate and EndTime. I would like to know how to create a find request to return all records which are >=the current Date and time. I am really stuck on this. Any help would be greatly appreciated. I thought I had it figured out, but I was wrong.

[Lee Smith]

DO NOT DOUBLE POST!

PLEASE Refrain from doing this.

I Answered this in your other post. If the answer did help, reply there.

TIA

Lee

[dleggett]

I tried to delete the previous post when I thought I had found my answer. Unfortunatetly that did not work. There is also no posting of your answer in that post. Perhaps the fact that I tried to delete the post stop your reply. Obviously if I had seen your reply, I would not have asked the question again. Would it be possible for you to repost your answer here? I would really appreciate it.

[Lee Smith]

In the Future, please don't delete your post.

this is a learning forum and believe it or not, some members first do a search of the forums for keywords of their questions (i.e. Find dates between two dates), and if they get a hit, it saves all of time, because we don't have to keep answering the same questions over and over again. At least that's the theory of the process.

Anyway, the answer I posted to your other post was to do a AND find.

This is a simple process and very basic in Finds, all you need to do is to put today's date in your first field, and the Equal to or Less than sign and the date of your second criteria in your second date field. This should produce all of the records with today's date and have a date equal to your second date, or less then the second date.

HTH

Lee

[dleggett]

I guess I'm confused. I only have one date field, and one time field. Do I need to make 2 date fields in my search page? And how does this help with records which have date=today and a time < current time?

[dleggett]

Ok, here is what I tried and I think it is working. I made a calc field which is:

StatusID=If(EndDate > Status(CurrentDate), "1", If(EndDate = Status(CurrentDate), If(EndTimeEST > Status(CurrentTime), "1", "0"), "0"))

I can now do a find for Records where StatusID=1

Does that sound resonable, or is there a better solution?

[Lee Smith]

That is another basic find request.

To find records in the same date field, you use the "..." range find

All you need to do is to type in your date field something like this:

09/18/2003...09/30/2003

and you can do the same thing with your time field if it is necessary.

08:00...17:00

This will find all records between 09/18/2003 at 8:00 AM and 09/30/2003 and 5 PM.

HTH

Lee

BTW, that was why used "Find dates between two dates" as my hint at searching the Forums for Keywords.

[dleggett]

Gotcha, Thanks alot for the help.

[dleggett]

Thanks for your suggestion, but I realized that it still doesn't give what I need. What I want is to find all records which are greater than todays date and time, not a range of dates and times as your example above shows. For example, the record will have a date = 9/22/03 and a time of 12:00pm. If today is 9/22/03 and it is 11:59am, the find will return this record. If today is 9/22/03 and it is 12:01, it doesn't. I tried using the calculation I listed above, but that calculation isn't updated in the record as the time changes, so it is only useful if I manually change the date or time in the record. I have also tried to put this logic into a script, but it only works on 1 record, not all my records. Is there a way to make it work on all my records. Again, any and all help would be appreciated.

[BobWeaver]

I was going to jump in here a while ago when I noticed that same problem, but I thought you had solved it with your calculation field StatusID which is probably as good a solution as any.

Another calculated field technique is to create a calculated field with this formula:

(EndDate-Status(CurrentDate))*86400+(EndTime-Status(CurrentTime))

Then search for values greater than 0.

The problem with both this calculation, and your previous calculated field is that they cannot be indexed. So the search will be slow if you have a lot of records.

The other option is to do a find with multiple requests:

Request 1: EndDate>Status(CurrentDate)

Request 2: EndDate=Status(CurrentDate), EndTime>Status(CurrentTime)

The advantage to this technique is that you are now searching on fields that are indexed, and so the search should be much faster.

[dleggett]

Hi BobWeaver

thanks for the advice. The one problem I seem to be having with multiple searches is that the second search seems to wipe out the old search results. I am a bit of a newbie on this, but there must be something very basic I am missing. Could you offer any suggestions?

[Fenton]

What Bob was saying is 2 "requests," not 2 Finds.

Go to Layout [

Find DateTime.zip