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 开发者_StackOverflow中文版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.

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.

The mysql function timestampdiff should do what you need

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

The unit value should be MINUTE or DAY

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.