sql - 日期和按日期排序的postgresql案例

Question

我试图按特定日期过滤结果，但是当我使用下面的代码时，我只会得到 CloseDate 与当前日期匹配的结果。当 CloseDate 为 Null 时，它不包括 ResolvedDate 的结果。我试过:

(CASE WHEN (Closedate IS Null) then ResolvedDate, ELSE CloseDate END) AS FinalDate

然后它说:

 "column FinalDate does not exist"

还有其他方法吗？

这是到目前为止的代码。感谢您的帮助。

SELECT
id,
(CASE WHEN (Closedate IS Null) then ResolvedDate, ELSE CloseDate END),
FROM cases
WHERE (EXTRACT (month FROM Closedate) = EXTRACT(month FROM current_date))
AND  ( EXTRACT(day from Closedate) = EXTRACT(day FROM current_date)) 

Answer 1

假设您还想匹配年份:

SELECT id, COALESCE(Closedate, ResolvedDate) AS cdate
FROM   cases
WHERE  date_trunc('day', COALESCE(Closedate, ResolvedDate))
     = date_trunc('day', now())

根据文档:COALESCE , date_trunc()

如果你想忽略年份:

WHERE  to_char(COALESCE(Closedate, ResolvedDate), 'MMDD')
     = to_char(now(), 'MMDD')

更多一点:
How do you do date math that ignores the year?