Solved

Time compare

Posted on 2009-05-19
7
300 Views
Last Modified: 2012-05-07
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.
0
Comment
Question by:dudeatwork
7 Comments
 
LVL 19

Expert Comment

by:erikTsomik
Comment Utility
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
 

Author Comment

by:dudeatwork
Comment Utility
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
Comment Utility
> 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 52

Expert Comment

by:_agx_
Comment Utility
> though it is probably the ideal method.

... though it is probably NOT the ideal method.
0
 
LVL 27

Expert Comment

by:azadisaryev
Comment Utility
@_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
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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
 

Author Closing Comment

by:dudeatwork
Comment Utility
Nice try though, thanks.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now