CODEWITHSUNDEEP
mysqlcoldfusioncfquerycfqueryparam
Nich
Nich

Reputation: 283

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.

Upvotes: 1

Views: 664

Answers (3)

Ciaran Archer
Ciaran Archer

Reputation: 12456

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.

Upvotes: 6

Tyler Clendenin
Tyler Clendenin

Reputation: 1489

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.

Upvotes: 0

Lee
Lee

Reputation: 1662

The mysql function timestampdiff should do what you need

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

The unit value should be MINUTE or DAY

Upvotes: 1

Related Questions