I’ve been creating some views lately where I have encountered what I consider unreasonable rigidity with the COALESCE Function when using DATE and TIMESTAMP functions. Let me give some examples.
Here is an example that I think works well. Note that Date_Opened type is of type DATE and NOW() returns a TIMESTAMP.
select Case_Open.Case_Number,DATEDIFF(‘DAY’, Case_Open.Date_Opened, NOW()) as AGE FROM Case_Open
And here is the same thing but using COALESCE and you can see the exception given.
select Case_Open.Case_Number, COALESCE(Case_Open.Date_Opened, Now()) FROM Case_Open
Exception: expressions in ELSE clause must be of the same or compatible types as those in the THEN clauses.
It seems odd to me that COALESCE would be this sensitive between a DATE and TIMESTAMP.
My desired statement would be something like this:
DATEDIFF(‘DAY’, Case_Open.Date_Opened, COALESCE(Case_Close.Date_Closed, Now())) as AGE
The logic being:
Take the beginning date
Take either the ending date or the current time
Give the difference in days
If anyone has a workaround I’d appreciate it.
I’ve tried to include a CAST but with the same result.
DATEDIFF(‘DAY’, Case_Open.Date_Opened, COALESCE(Case_Close.Date_Closed, CAST(Now() as DATE))) as AGE