How to get future and past dates in mm/dd/yyyy and dd/mm/yyyy format in Oracle SQL 18C?-CodePudding

I want to get display of future and past dates in mm/dd/yyyy and dd/mm/yyyy format in Oracle SQL 18C using SQL functions, so I want the code for it. I tried code select sysdate from dual and I get the output 21-JAN-23, but I want output of future and past dates like 23/11/2033 and 16/12/2009 in mm/dd/yyyy and dd/mm/yyyy format.

CodePudding user response：

Format date using TO_CHAR() function

SELECT
  TO_CHAR( SYSDATE, 'FMMonth DD, YYYY' )
FROM
  dual;

The output would be:

August 1, 2017

CodePudding user response：

Creating a Future or Past Date

In Oracle, a DATE is a binary data type that ALWAYS consists of 7 bytes representing century, year-of-century, month, day, hour, minute and second and is NEVER stored in any particular human-readable format.

Therefore, if you want to get a DATE data type in a particular format then it is impossible as dates never have any format when they are stored.

If you want to get a date you can use:

A date literal:
```
SELECT DATE '2023-12-31' FROM DUAL;
```

or, the TO_DATE function:

SELECT TO_DATE('31/12/2023', 'MM/DD/YYYY') FROM DUAL;

Displaying Dates in a Client Application

However, if the problem is how to display a date in a particular format then you need to convert the binary DATE value to a string.

Most client applications (SQL*Plus, SQL Developer, TOAD, C#, Java, etc.) will implicitly convert a binary date to something that is human-readable when they display it and will have settings in the application that determine the default format that it applies to dates.

For SQL*Plus and SQL Developer, you can modify the NLS_DATE_FORMAT session parameter to change how that client application displays dates (note: this does not change how Oracle stores the dates internally, only how it is displayed by the client).

For example:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

or:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

And then the client application will display dates in that format when you use a SELECT statement.

For other client applications you will need to check the documentation for that application.

Explicitly Formatting Dates as Strings

If you want to display a DATE in a particular format independent of any settings in the client application then you will need to convert the date to a string.

Using TO_CHAR:

SELECT TO_CHAR(DATE '2023-12-31', 'MM/DD/YYYY') AS formatted_date FROM DUAL;

Or, if you are generating the date and formatting it (rather than taking an existing date and formatting it) then you could just use a string literal:

SELECT '31/12/2023' AS formatted_date FROM DUAL;