0

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.

James Z
  • 12,209
  • 10
  • 24
  • 44
adityarajsahoo
  • 1
  • 2
  • The function to convert a date into a string of a given format is `TO_CHAR`. We don't use this very often, however, because we want our apps to display dates according to user settings and we want our apps to be aware of the values being dates. So we just select the dates and let the app handle the display things. There are some rare situations, though, when we want a certain format, namely when we want to export the result into some text file. In that case we use `TO_CHAR`. – Thorsten Kettner Jan 21 '23 at 11:58

2 Answers2

1

Format date using TO_CHAR() function

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

The output would be:

August 1, 2017
A. Rokbi
  • 503
  • 2
  • 8
1

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;
MT0
  • 143,790
  • 11
  • 59
  • 117