Oracle SQL: Show MONTH YEAR in a date format-CodePudding

I would like to show a calculated column that is created using the following:

to_char(
        COALESCE(
            date1,
            date2,
            date3
        ),
        'MONTH - YYYY'
    ) month_year_column

but the problem is this returns a string need a date field instead

CodePudding user response：

In Oracle, a DATE is a binary data type that consists of 7 bytes representing: century, year-of-century, month, day, hours, minutes and seconds. It ALWAYS has all of those components and is NEVER stored with any particular (human-readable) format.

Therefore, it is impossible to have a DATE data type and a particular formatting.

If you want a DATE value where the day and time components are set to midnight of the first day of the month then you can use:

TRUNC( COALESCE(date1, date2, date3 ), 'MM') AS month_year_column

Note: this will then be formatted by the client application you are using to access the database according to its default date formatting rules (which may not show all the components of the date); this does not change that the database will store the value as a 7-byte binary value regardless of how the client application choses to display it.

If you do want it with a particular formatting then you cannot use a (unformatted) DATE and need to convert the value to another data type that can be formatted, such as a string and you can use your code:

TO_CHAR( COALESCE( date1, date2, date3 ), 'MONTH - YYYY' ) AS month_year_column

CodePudding user response：

I guess you can use NVL instead of COALESCE to avoid losing the DATE data type.

NVL (date1, NVL (date2, date3))