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.
dudeatworkAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
> Can I concatenate in the query report_date & report_time as mydate?

I believe Access stores date/time values as numbers internally. So just add them together. Then compare that value to whatever date and time value you need (24 hours ago, 24-28 hours, etc).  It should work fine, though it is probably the ideal method.  (I prefer to store date and time values in one field, because of exactly this type of scenario.)
<!--- create the time ranges you need --->
<cfset dateTimeNow = now()>
<cfset twentyFourHoursAgo = dateAdd("d", -24, dateTimeNow)>
<cfset twentyEightHoursAgo = dateAdd("d", -28, dateTimeNow)>
....
 
<cfquery name="lessThan24Hrs" datasource="YourDatasource">
	SELECT 	report_date, report_time, OtherColumns ...
	FROM 	yourTable
	WHERE	(report_date+ report_time) > <cfqueryparam value="#twentyFourHoursAgo#" cfsqltype="cf_sql_timestamp">
</cfquery>
 
<cfquery name="from24To28Hours" datasource="YourDatasource">
	SELECT 	report_date, report_time, OtherColumns ...
	FROM 	yourTable
	WHERE	(report_date+ report_time)	BETWEEN 
			<cfqueryparam value="#twentyFourHoursAgo#" cfsqltype="cf_sql_timestamp"> AND
			<cfqueryparam value="#twentyEightHoursAgo#" cfsqltype="cf_sql_timestamp">
</cfquery>

Open in new window

0
 
erikTsomikSystem Architect, CF programmer Commented:
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>
0
 
dudeatworkAuthor Commented:
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.
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
_agx_Commented:
> though it is probably the ideal method.

... though it is probably NOT the ideal method.
0
 
azadisaryevCommented:
@_agx_:  :) typos again! your code is getting records from 24 and 28 DAYS ago... and 28 should be 48 according to the question...

Azadi
0
 
_agx_Commented:
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)>
0
 
dudeatworkAuthor Commented:
Nice try though, thanks.
0
All Courses

From novice to tech pro — start learning today.