GREATEST DATETIME Value When 1 Argument Maybe NULL

Posted . Visible to the public.

I have a table with 2 DATETIME columns, create_date and modify_date
I want to SELECT the GREATEST of the 2 columns.

SELECT GREATEST(asset_details.create_date, asset_details.modify_date) as pubdate
FROM asset_details

However, modify_date could be NULL (in fact most cases it is). The problem is GREATEST in MySQL 5 returns NULL if any of the arguments is NULL.

How can I write the SELECT with the results I need?

This Works.

SELECT GREATEST(COALESCE(asset_details.create_date, asset_details.modify_date), COALESCE(asset_details.modify_date, asset_details.create_date)) as pubdate
FROM asset_details

Profile picture of Alexandru Catalin Trandafir
Alexandru Catalin Trandafir
Posted by Alexandru Catalin Trandafir to Alexandru Catalin Trandafir's deck (2013-03-08 11:48)