计算DB2中两个日期之间的天数？ [英] Calculating how many days are between two dates in DB2?

问题描述

我需要在DB2中的两个日期之间得到差异。我尝试了几个不同的查询，但似乎没有任何工作。所以基本上我需要得到的就是这样的。

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.

 SELECT DAYS (CURRENT DATE) - DAYS (DATE(CHDLM)) FROM CHCART00 WHERE CHSTAT = '05';

我知道如果我删除CHDLM并指定一个像2012-02-20这样的日期，它可以工作但是我需要能够对表中的该字段运行。我也尝试一个朋友给我的这个查询也不起作用。

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.

 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';

请任何谢谢

推荐答案

我认为@Siva在正确的轨道上（使用 DAYS（）），但嵌套的 CONCAT（）正在让我晕眩，这是我的采取。

哦，没有必要引用 sysdummy1 ，因为您需要从表中拉取。

另外，不要使用隐式连接语法 - 它是被认为是一个SQL反模式。

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.

我在CTE中将日期转换包含在可读性中，但是没有任何东西阻止你内联。

I'be wrapped the date conversion in a CTE for readability here, but there's nothing preventing you from doing it inline.

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

这篇关于计算DB2中两个日期之间的天数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！