Solved

datediff in cfml

Posted on 2010-09-22
8
434 Views
Last Modified: 2012-06-27
Hello experts.
I want to use the datediff function i had using mssql now with mysql but i can't get it work.
Any help?
<cfquery name="gettime" datasource="#request.dsn#">
       SELECT requestpw,user_ID
       From users
       Where uuidpw = <cfqueryparam cfsqltype="cf_sql_varchar" value="#url.resetpw#">
     </cfquery>
     <cfif gettime.recordcount NEQ 0>
         <cfscript>
             date1 = #LSParseDateTime(gettime.requestpw)#;
         date2 = now();
             REQUESTEXITS = 1;
             </cfscript>
          <cfif dateDiff("h",date1, Date2) lt 24 >
            <cfset oldrequest = 1 />
           <cfelse>
         <cfset oldrequest = 0 />
        </cfif>
0
Comment
Question by:Panos
  • 4
  • 2
8 Comments
 
LVL 1

Assisted Solution

by:gigipaul
gigipaul earned 150 total points
ID: 33735665
DateDiff in Mysql will give only Days.
You need to use SEC_TO_TIME(dt1-dt2) will give the difference in seconds and see if its more than 24 hours.
Hope this helps
0
 
LVL 52

Expert Comment

by:_agx_
ID: 33735712
>> i can't get it work.

The code posted isn't using database functions, it's using CF's DateDiff() function.  Can you explain what's not working... rre you getting an error message?
0
 
LVL 2

Author Comment

by:Panos
ID: 33735862
Hi.
agx. You are right.It is cf code but for the following test code i get output 0.
<cfset set = '0879E1C5-DF40-8B46-5E7036434BBF14C2'>
<cfquery name="gettime" datasource="#request.dsn#">
       SELECT requestpw,user_ID
       From users
       Where uuidpw = <cfqueryparam cfsqltype="cf_sql_varchar" value="#set#">
     </cfquery>
 <cfif gettime.recordcount NEQ 0>
         <cfscript>
             date1 = #LSParseDateTime(gettime.requestpw)#;
         date2 = now();
             REQUESTEXITS = 1;
             </cfscript>
          <cfif dateDiff("h",date1, Date2) lt 24 >
            <cfset oldrequest = 1 />
           <cfelse>
         <cfset oldrequest = 0 />
        </cfif>
      </cfif>
     
      <cfset dif = dateDiff("h",Date2, date1)>
        <cfoutput>#dif#</cfoutput>

( requestpw value in db = 2010-09-22 16:59:23)
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 2

Author Comment

by:Panos
ID: 33735926
Hi gigipaul
24 hours are 1 day.That's it what i need.The problem is somewhere else.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 350 total points
ID: 33737905
>> date1 = #LSParseDateTime(gettime.requestpw)#;
>> date2 = now();
<cfif dateDiff("h",date1, Date2) lt 24 >

Why use LSParseDateTime() there? The value from the db should already be a date object.  I'm wondering if that's doing something unexpected to the value.  

Display both dates, and the dateDiff what are the results?
<cfoutput>
#date1#  #date2#   #dateDiff("h",date1, Date2)#
</cfoutput>
0
 
LVL 2

Author Comment

by:Panos
ID: 33741360
Hi.
I don't know what the problem wasbut it is working now with and without LSParseDateTime().
Thank you both.
0
 
LVL 2

Author Closing Comment

by:Panos
ID: 33741374
Thank you both for your help.I gave some points to expert  gigipaul for his interest in my question.It was my mistace to include the mysql zone.
regards
panos
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 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