Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Select MAX on CASE & CONVERT to DATE

KelvinFeb 24 2020 — edited Feb 24 2020

Hi All,

A database holds the Day, Month and Year in separate columns, some Days and Months are NULL so I'm replacing the NULLS with 1

With the following SQL I get dates like  

1-1-2001, 14-1-2000, 20-10-2003

What I'm now trying to do is select the most recent date, so Ideally entering a MAX(). Unfortunately this isn't working correctly as it doesn't see the entrys as dates.

How can I convert to date, perform a MAx whist using CASE to pull through 1 instead of NULL??

SELECT

    CASE WHEN PUB_DATE_DAY IS NULL THEN 01 ELSE PUB_DATE_DAY END

    ||'-'

    || CASE WHEN PUB_DATE_MONTH IS NULL THEN 01 ELSE PUB_DATE_MONTH END

    ||'-'

    || PUBLICATION_DATE_YEAR_INTEGER

FROM pure.pub_status;

Kind regards, Kelvin.

This post has been answered by BrunoVroman on Feb 24 2020
Jump to Answer
Comments
Post Details
Added on Feb 24 2020
4 comments
2,747 views