CODEWITHSUNDEEP
sqlstringoracle-databasedatetimeoracle11g
xpetta
xpetta

Reputation: 718

Convert Date to specific format in Oracle SQL

I would like to convert the date to this format "2020-08-19" in Oracle SQL.

I did try doing this but it returns as 19-AUG-20

SELECT TO_DATE('2020-08-19', 'YYYY-MM-DD') FROM DUAL;

OUTPUT

19-AUG-20

I want the output in the below format:

2020-08-19

Could anyone please help me with this? Thanks in advance!

Upvotes: 3

Views: 23897

Answers (1)

GMB
GMB

Reputation: 222492

If you have a date that you want to display as a formatted string, then use TO_CHAR():

SELECT TO_CHAR(DATE '2020-08-19', 'YYYY-MM-DD') FROM DUAL;

In the function, DATE '2020-08-19' is a literal date: that's a date datatype.

As for your original code:

SELECT TO_DATE('2020-08-19', 'YYYY-MM-DD') FROM DUAL;

What it does is interpret the input string as a date, given the format specified as the second argument. This returns a date, that is then displayed according to the NLS_DATE_FORMAT parameter of your database or session. You can change the parameter value at session level like so:

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

Now running the same query gives you the result that you want.

Upvotes: 7

Related Questions