Search code examples
mysqlcoldfusioncfquerycfqueryparam

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

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

  • 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.