[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

datediff in cfml

Posted on 2010-09-22
8
Medium Priority
?
445 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
7 Comments
 
LVL 1

Assisted Solution

by:gigipaul
gigipaul earned 600 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 53

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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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 53

Accepted Solution

by:
_agx_ earned 1400 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses

590 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