在 Coldfusion CFQUERY 中按分钟而不是按日比较日期时间值
这是我的疑问。
<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>
它能够给我所有expdatetime小于#yourdate#的结果，唯一的问题是它只显示相差一整天的结果，而不显示相差分钟的结果。因此，相差小于 #yourtime# 分钟的 expdatetime 将不会显示在结果中，除非相差至少一天。
如何优化该查询的最小精度？
我知道两个可能有用的函数，DateDiff 和 DateDiff。 DateCompare，但我不知道如何在查询中应用它们。
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
如有任何帮助，我们将不胜感激。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
尝试使用 CF_SQL_TIMESTAMP，即：
我不确定您是否需要 maxlength 属性。我发现 CF 映射需要时间戳才能获得所需的精度。
Try using
CF_SQL_TIMESTAMP, i.e:I'm not sure if you'll need the
maxlengthattribute. I've found that the CF mapping needs to be timestamp to get the required precision.mysql函数 timestampdiff应该做你需要的
单位值应该是分钟或天
The mysql function timestampdiff should do what you need
The unit value should be MINUTE or DAY
我本人是 MS-SQL 人员，但使用 DateDiff 函数并计算结果是正数、负数还是零应该会告诉您所需的信息。
但是，我不确定您为什么需要这样做。 IS LESS THAN 测试应该产生所需的结果，我唯一能想到的是，如果您的 #yourDate# 变量不够具体，即不包括时间，那么您的时间将被解释为 00:00:00，这会扭曲你的结果。我建议确保 #yourDate# 中的数据尽可能具体。
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.