Force the format of a DATETIME or INTERVAL value
The database server always displays the components of
an INTERVAL or DATETIME value in the order year-month-day
hour:minute:second.fraction. It does not refer to the date
format that is defined to the operating system, as it does when it
formats a DATE value.
You can write a SELECT statement that
displays the date part of a DATETIME value in the system-defined format.
The trick is to isolate the component fields with the EXTEND function
and pass them through the MDY() function, which converts them
to a DATE. The following code shows a partial example:
SELECT ... MDY (
EXTEND (DATE_RECEIVED, MONTH TO MONTH),
EXTEND (DATE_RECEIVED, DAY TO DAY),
EXTEND (DATE_RECEIVED, YEAR TO YEAR) )
FROM RECEIPTS ...