Tutorial :Compare DateTime values by Min and not by Day in Coldfusion CFQUERY



Question:

This is the query that I have.

<cfquery name="qryname" datasource="dsn">  UPDATE ticketlist  SET status  = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="Expired">   WHERE expdatetime <   <cfqueryparam value = "#yourDate#" cfsqltype = "CF_SQL_DATE" maxLength = "19">  </cfquery>  

It is able to give me all the results who's expdatetime is less than #yourdate#, the only problem is that it only shows me the results who's difference is a whole day, but not those who's difference is in min. So, expdatetime who's difference are less than #yourtime# by min's will not show in the results, unless the difference is by at least a day.

How can this query be optimized for min precision?

I know of two functions that may be of use, the DateDiff & the DateCompare, but I do not know how to apply them in the query.

DateDiff("datepart", "date1", "date2")

DateCompare("date1", "date2" [, "datePart"])

DatePart Precision

* s Precise to the second  * n Precise to the minute  * h Precise to the hour  * d Precise to the day  * m Precise to the month  * yyyy Precise to the year  

Any help will be appreciated.


Solution:1

Try using CF_SQL_TIMESTAMP, i.e:

<cfqueryparam value = "#yourDate#" cfsqltype = "CF_SQL_TIMESTAMP">  

I'm not sure if you'll need the maxlength attribute. I've found that the CF mapping needs to be timestamp to get the required precision.


Solution:2

The mysql function timestampdiff should do what you need

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)  

The unit value should be MINUTE or DAY


Solution:3

I am a MS-SQL guy myself, but using a DateDiff function and calculating whether the result is positive, negative, or zero should tell you the information you need.

But, I am not sure why you would need to do this. The IS LESS THAN test should produce the results needed, the only thing I can think of is if your #yourDate# variable is not specific enough, i.e. does not include the time, then your time will be interpreted as 00:00:00, which will skew your results. I would recommend making sure that the data in #yourDate# is as specific as possible.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »