postgresql case when date and sorting by date

Question

Im trying to filter results by a specific date but when I use the below code I only get results where CloseDate matches current date. It doesnt include the results from ResolvedDate when CloseDate is Null. I tried with :

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

but then it states:

 "column FinalDate does not exist"

Any other way I can do this?

Here is the code thus far. Appreciate your help.

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))

With you where clause as it is CloseDate can never be null anyway. However, you could just use COALESCE(CloseDate, ResolvedDate). It would also be a more sargable query if you did something like CloseDate >= current_date AND CloseDate < current_date + interval 1 day'` — GarethD
– GarethD, Commented Mar 10, 2014 at 22:29
Remove comma after ResolveDate..unless postgres is weird like that. — Mihai
– Mihai, Commented Mar 10, 2014 at 22:31
A table definition would be in order with such a question. We would need exact data types. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 10, 2014 at 22:31
Hi, my mistake on the extra comma, it wasnt supposed to be there. — exclusivebiz
– exclusivebiz, Commented Mar 10, 2014 at 22:32
You cannot reference column aliases in the where clause, you would need to repeat the expression, or put the expression in a subquery and then apply the where clause. — GarethD
– GarethD, Commented Mar 10, 2014 at 22:33

Community · Accepted Answer · 2017-05-23 12:21:29Z

2

Assuming you want to match the year as well:

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

Per documentation: COALESCE, date_trunc()

If you want to ignore the year:

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

A bit more on that:
How do you do date math that ignores the year?

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Mar 10, 2014 at 22:34

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

exclusivebiz Over a year ago

Thanks but I got a ERROR: function date_trunc(unknown, timestamp without time zone, timestamp without time zone) does not exist

Erwin Brandstetter Over a year ago

@exclusivebiz: That was probably the first draft of my answer which had an error. You must have missed the update, it works fine now.

CrazyMenConnected · Accepted Answer · 2014-03-10 22:51:34Z

1

You should use COALESCE function http://www.postgresql.org/docs/current/static/functions-conditional.html

Try this:

SELECT
  id,
  COALESCE(CloseDate, ResolvedDate) AS FinalDate
FROM
  cases
WHERE
  (EXTRACT (month FROM COALESCE(CloseDate, ResolvedDate)) = EXTRACT(month FROM current_date)) AND
  (EXTRACT (day from COALESCE(CloseDate, ResolvedDate)) = EXTRACT(day FROM current_date))

That should do it...

edited Mar 10, 2014 at 22:51

user330315

answered Mar 10, 2014 at 22:34

CrazyMenConnected

7126 silver badges23 bronze badges

2 Comments

CrazyMenConnected Over a year ago

Nice, I'm glad i could help! :-)

CrazyMenConnected Over a year ago

Thanks for the edit @a_horse_with_no_name that link was out of date.

Collectives™ on Stack Overflow

postgresql case when date and sorting by date

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related