Solved

datediff in cfml

Posted on 2010-09-22
8
432 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
problem with finding cfcomponent on cfselect bind 7 86
populate select menu with array from another select menu 3 70
cfspreadsheet 15 96
Database set up 5 73
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

816 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

11 Experts available now in Live!

Get 1:1 Help Now