asked on

Time compare

I am trying to create a code set that will tell me how old reports are. For example. I have a field in the ACCESS db that is "report_date" and is a date/time. I want to break the reports down by
1. Less than 24 hours
2. 24-48 hours
3. 3-7 days
4. 7+ days

I know I can use datecompare, but I am having trouble with the less than 24 hours and the 24-48 hours part.

How can I take report_date < 24 hours and cull those records from the query and report_date between 24-48 hours with a date.

erikTsomik

try this code it works just fine
<cfset lTime1="2/18/2008 11:31:15 AM">
<cfset lTime2 = "2/15/2008 15:31:15 PM">
<cfif DateDiff("h",lTime1,lTime2) gt 24 and DateDiff("h",lTime1,lTime2) lt 48>
Hello
<cfelse>
Not Hello
</cfif>

dudeatwork

ASKER

What if the table is broken to two columns report_date and report_time. Can I concatenate in the query report_date & report_time as mydate?
I am trying to get the condition in the cfquery for the record sets.

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

> though it is probably the ideal method.

... though it is probably NOT the ideal method.

azadisaryev

@_agx_: :) typos again! your code is getting records from 24 and 28 DAYS ago... and 28 should be 48 according to the question...

Azadi

_agx_

Ack! Thank you :) I was focusing on the Access db differences. Just a _slight_ difference ;-)

Thanks to azadi, the corrected date logic is:

<cfset twentyFourHoursAgo = dateAdd("h", -24, dateTimeNow)>
<cfset twentyEightHoursAgo = dateAdd("h", -28, dateTimeNow)>

dudeatwork

ASKER

Nice try though, thanks.