SQL 计算 DB2 中两个日期之间的天数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9538632/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating how many days are between two dates in DB2?
提问by jorame
I need to get the difference in days between two dates in DB2. I tried a couple of different queries but nothing seems to be working. So basically what i need to get is something like this.
我需要获得 DB2 中两个日期之间的天数差异。我尝试了几个不同的查询，但似乎没有任何效果。所以基本上我需要得到的是这样的。
SELECT DAYS (CURRENT DATE) - DAYS (DATE(CHDLM)) FROM CHCART00 WHERE CHSTAT = '05';
I know that if I remove CHDLM and specify a date like '2012-02-20' it works but I need to be able to run this against that field in the table. I also try this query that was given to me by a friend by is not working either.
我知道，如果我删除 CHDLM 并指定一个像“2012-02-20”这样的日期，它会起作用，但我需要能够针对表中的该字段运行它。我也尝试了这个由朋友提供给我的查询也不起作用。
select days (current date) - days (date(select concat(concat(concat(concat(left(a2.chdlm,4),'-'),substr(a2.chdlm,4,2)),'-'),right(a2.chdlm,2))) from sysibm.sysdummy1 a1, chcart00 a2 where chstat = '05';
Please any help will be greatly appreciate it. Thanks
请任何帮助将不胜感激。谢谢
回答by Clockwork-Muse
I think that @Siva is on the right track (using DAYS()), but the nested CONCAT()s are making me dizzy. Here's my take.
Oh, there's no point in referencing sysdummy1, as you need to pull from a table regardless.
Also, don't use the implicit join syntax - it's considered an SQL Anti-pattern.
我认为 @Siva 走在正确的轨道上（使用DAYS()），但嵌套的CONCAT()s 让我头晕目眩。这是我的看法。
哦，引用没有意义sysdummy1，因为无论如何你都需要从表中提取。
另外，不要使用隐式连接语法 - 它被认为是 SQL 反模式。
I'be wrapped the date conversion in a CTE for readability here, but there's nothing preventing you from doing it inline.
为了便于阅读，我将日期转换包装在 CTE 中，但没有什么可以阻止您进行内联。
WITH Converted (convertedDate) as (SELECT DATE(SUBSTR(chdlm, 1, 4) || '-' ||
SUBSTR(chdlm, 5, 2) || '-' ||
SUBSTR(chdlm, 7, 2))
FROM Chcart00
WHERE chstat = '05')
SELECT DAYS(CURRENT_DATE) - DAYS(convertedDate)
FROM Converted
回答by E_X
I faced the same problem in Derby IBM DB2 embedded database in a java desktop application, and after a day of searching I finally found how it's done :
我在 Java 桌面应用程序中的 Derby IBM DB2 嵌入式数据库中遇到了同样的问题，经过一天的搜索，我终于找到了它是如何完成的：
SELECT days (table1.datecolomn) - days (current date) FROM table1 WHERE days (table1.datecolomn) - days (current date) > 5
for more information check this site
欲了解更多信息，请查看此网站
回答by Isaac
values timestampdiff (16, char(
timestamp(current timestamp + 1 year + 2 month - 3 day)-
timestamp(current timestamp)))
1
=
422
values timestampdiff (16, char(
timestamp('2012-03-08-00.00.00')-
timestamp('2011-12-08-00.00.00')))
1
=
90
---------- EDIT BY galador
----------由加拉多编辑
SELECT TIMESTAMPDIFF(16, CHAR(CURRENT TIMESTAMP - TIMESTAMP_FORMAT(CHDLM, 'YYYYMMDD'))
FROM CHCART00
WHERE CHSTAT = '05'
EDIT
编辑
As it has been pointed out by X-Zero, this function returns only an estimate. This is true. For accurate results I would use the following to get the difference in days between two dates a and b:
正如 X-Zero 所指出的，这个函数只返回一个估计值。这是真的。为了获得准确的结果，我将使用以下方法来计算两个日期 a 和 b 之间的天数差异：
SELECT days (current date) - days (date(TIMESTAMP_FORMAT(CHDLM, 'YYYYMMDD')))
FROM CHCART00
WHERE CHSTAT = '05';
回答by Siva Charan
It seems like one closing brace is missing at ,right(a2.chdlm,2)))) from sysibm.sysdummy1 a1,
似乎缺少一个右括号 ,right(a2.chdlm,2)))) from sysibm.sysdummy1 a1,
So your Query will be
所以你的查询将是
select days(current date) - days(date(select concat(concat(concat(concat(left(a2.chdlm,4),'-'),substr(a2.chdlm,4,2)),'-'),right(a2.chdlm,2)))) from sysibm.sysdummy1 a1, chcart00 a2 where chstat = '05';
回答by David Faber
Wouldn't it just be:
不就是：
SELECT CURRENT_DATE - CHDLM FROM CHCART00 WHERE CHSTAT = '05';
That should return the number of days between the two dates, if I understand how date arithmetic works in DB2 correctly.
如果我理解日期算术在 DB2 中是如何正确工作的，那应该返回两个日期之间的天数。
If CHDLM isn't a date you'll have to convert it to one. According to IBM the DATE() function would not be sufficient for the yyyymmdd format, but it would work if you can format like this: yyyy-mm-dd.
如果 CHDLM 不是日期，则必须将其转换为日期。根据 IBM 的说法，DATE() 函数对于 yyyymmdd 格式是不够的，但是如果您可以像这样设置格式：yyyy-mm-dd，它会起作用。
