SQL – CASE statement: “Inconsistent datatypes: expected DATE got CHAR”

I have written an CASE statement (as a apart of a longer query) that looks for invoices in a table, if it finds an invoice, it returns the last invoice date, otherwise a string “No Invoices”.

I understand that data types should be consistent, this is why I have converted the date to string, but this way the date can’t be sorted in Excel as it is exported as a string.

How can I make this work and keep the date in the correct date format without having to convert it to a string?

Thank you!

case when exists (select * from AP_INVOICES_ALL i where i.VENDOR_SITE_ID = s.VENDOR_SITE_ID)
     then (select to_char(max(i.INVOICE_DATE)) from AP_INVOICES_ALL i where i.VENDOR_SITE_ID=s.VENDOR_SITE_ID)  
     else 'No invoices'
end as "AP Last Invoice Date"

Answer

I would just use NULL to represent the lack of invoice date. But if you need to use a string, one option is to use a date in a sortable format:

to_char(max(i.INVOICE_DATE), 'YYYY-MM-DD')

Advertisement

Answer