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 ...