CODEWITHSUNDEEP
oracle-databasedatedatetimemax
Abby Stocker
Abby Stocker

Reputation: 1

Changing format of date without using to_char - Oracle

I have to get the max payment date on an invoice and I am having trouble with the date format. I do not need the max in this formula as I am using the format in a reporting tool that is pulling the max from what it finds for me.

Using "to_char({datefield},'mm/dd/yyyy')" works for displaying that date the way we would like BUT when you use summary function MAX it does not pull the correct date because it is looking at a string and not a date (it will think 12/3/21 is larger than 3/2/22).

Another thing I have tried is trunc - "trunc({datefield})" which gives us the correct max date but it changes the formatting. For example if the date prior to the formula being applied is "8/12/21 12:00:00:000" the trunc formula will display it as 12-08-21 which is horribly wrong.

Long story short is I need a way to change a date/time to date with the format of 'mmmm/dd/yyyy' WITHOUT converting it to a string with something like to_char. Thank you!!!!

Upvotes: 0

Views: 263

Answers (1)

MT0
MT0

Reputation: 168061

A DATE is a binary data type consisting of 7 bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has all of those components and it is NEVER stored with any (human-readable) format.

What you are seeing when a date is displayed is the client application you are using to access the database making a decision to be helpful to you, the user, and display the binary DATE provided by the database in a human-readable format.

If you want to change how the DATE is displayed then you either need to:

  • Change the settings on the client application that controls how it formats dates when it displays them to you; or
  • Change the data-type so that it is no longer a DATE (which does not have a format) to a data type where the values of the date can be formatted (such as a string). You can do this using TO_CHAR.

If you want to find the maximum then do it BEFORE applying the formatting:

SELECT TO_CHAR(MAX({datefield}),'mm/dd/yyyy')
FROM   your_table;

Upvotes: 1

Related Questions