DB2 查询中的 DATEDIFF [英] DATEDIFF in DB2 query

问题描述

我有一个来自 mysql 的查询，该查询一直在我最近迁移到 DB2 的表上运行.

I have a query from mysql that has been running on a table I recently migrated to DB2.

由于下面的行，查询现在在 DB2 上失败，说找不到 DATEDIFF.我假设只是因为这不是 db2 上的有效函数.

The query now fails on DB2 due to the line below, saying that DATEDIFF cannot be found. I'm assuming only because this isn't a valid function on db2.

在 DB2 上是否有与此相同的功能可以保持性能和功能?

Is there an equivalent to this on DB2 that will maintain performance as well as function?

SELECT DISTINCT
    LEAST(180, DATEDIFF(curdate(), start_date)) as days
FROM table2
where expire_date > curdate()

推荐答案

我使用 DAYS() 函数将日期转换为数字序号，然后减去它们，如下所示:

I use the DAYS() function to convert the dates to numeric sequential numbers and then just subtract them, as in:

SELECT DISTINCT
    LEAST(180, DAYS(curdate()) - DAYS(start_date)) as days
FROM table2
where expire_date > curdate()

根据 DB2 的手册，DAYS() 返回:结果比 0001 年 1 月 1 日的天数多 1".

According to DB2's manual, DAYS() returns: "The result is 1 more than the number of days from January 1, 0001".

这篇关于DB2 查询中的 DATEDIFF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！