Need help with Oracle SQL Formatting. I have the below code which outputs the data (MINNEEDTIME) in DD-MM-YY format. Need to change this to MM/DD/YYYY HH:MI AM format. Tried TO_CHAR for converting and that threw an error. Any assistance will be appreciated. Thank you

min(case when ve.REASON in ('Need Time', 'WAITING Time') then ve.EVENT_DTIME end) as MINNEEDTIME

CodePudding user response：

You can use TO_CHAR(<date>, <format>) as in:

select to_char(current_date, 'MM/DD/YYYY HH:MI AM') from dual

In your case replace ve.EVENT_DTIME with:

to_char(ve.EVENT_DTIME, 'MM/DD/YYYY HH:MI AM')

CodePudding user response：

In Oracle, a DATE is a binary data type consisting of 7 bytes that represent: century, year-of-century, month, day, hour, minute and second. It ALWAYS contains those components and it is never stored with any human-readable format.

What you are seeing is the client application you are using to access the database receiving the (unformatted) binary data and trying to be helpful and applying its own format to the date.

You need to either:

• Explicitly convert the value from a DATE to a string and apply a format using the TO_CHAR function:

  TO_CHAR(
    min(
      case
      when ve.REASON in ('Need Time', 'WAITING Time')
      then ve.EVENT_DTIME
      end
    ),
    'MM/DD/YYYY HH12:MI AM'
  ) as MINNEEDTIME
  

• Or, you need to change how the client application you are using formats dates. You can either look for the settings/preferences in the application or some applications (for example, SQL*Plus or SQL Developer) will use the NLS_DATE_FORMAT session parameter which you can change using:

  ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH:MI AM';