Change date format in oracle query

Question

When running

 select processing_date from table;

i got this result "04-30-2020 20.12.49.978711" what i want to change the format of the result to "30-APR-20"

is there a way i can do that ?

i tried select to_date(processing_date,'mm-dd-yyyy') from table; but it gives me errors

any help ?

What does documentation say about to_date function parameter's type? Date format and date value are different. Format can be set up in your IDE or in SQLPlus with alter session set NLS_DATE_FORMAT = 'DD-MON-RR'. To select character string use to_char — astentx
– astentx, Commented Oct 29, 2020 at 13:37
@astentx - as regards the 'RR' format ... Please. No. Just. No. That format element was meant as a temporary fix to buy time in resolving the Y2k bug. Nearly 21 years ago. There is no reason not to use YYYY. — EdStevens
– EdStevens, Commented Oct 29, 2020 at 13:40

Gordon Linoff · Accepted Answer · 2020-10-29 18:20:03Z

1

You want to_char():

select to_char(processing_date, 'MM-DD-YYYY')

Dates are stored as an internal format, which you cannot change. If you want the date formatted in a particular way, then one solution is to convert to a string with the format you want.

EDIT:

The date appears to be a string. You can convert it to a date using:

select to_date(substr(processing_date, 1, 10), 'MM-DD-YYYY')

You can then either use as-is or use to_date() to get the format you really want.

edited Oct 29, 2020 at 18:20

answered Oct 29, 2020 at 13:25

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Temo Temo Over a year ago

it gave me ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

EdStevens Over a year ago

What is the data type of the column PROCESSING_DATE? Please update your question to show the exact query that returned that error, along with the error - all together, in context.

Temo Temo Over a year ago

it is varchar(29)

astentx Over a year ago

@TemoTemo For varchar you can convert it to date and then to char again to_char(to_date(substr('04-30-2020 20.12.49.978711', 1, 10), 'mm-dd-yyyy'), 'dd-MON-yy'). And cross your fingers no one will switch day's and month's places in future.

Collectives™ on Stack Overflow

Change date format in oracle query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related