CODEWITHSUNDEEP
sqloracle-databasedatetimeformatdate-conversion
Kushan Peiris
Kushan Peiris

Reputation: 167

Converting date format in Oracle sql

I have a date format in the form dd.mm.yyyy I want to convert it mm/dd/yyyy. Is it possible?

All the articles on the internet I read had dd/mm/yyyy to mm/dd/yyyy was possible and it worked when I tried.

But when I put dd.mm.yyyy it does not work? How can I fix it?

Please help me

Upvotes: 3

Views: 13722

Answers (2)

MT0
MT0

Reputation: 167774

I have a date format in the form dd.mm.yyyy

Assuming that you mean a DATE data type then, no, you don't have it in the format dd.mm.yyyy. A DATE data type value is stored within the database as a binary value in 7-bytes (representing century, year-of-century, month, day, hour, minute and second) and this has no format.

I want to convert it mm/dd/yyyy. Is it possible?

No, because DATE data types do not have a format.

However, what you probably meant to ask is:

I want to convert display as it mm/dd/yyyy. Is it possible?

Yes, use TO_CHAR with the appropriate format models:

SELECT TO_CHAR( date_value, 'mm/dd/yyyy' )
FROM   table_name

If you mean that you have a CHAR or VARCHAR2 data type storing date values in the format dd.mm.yyyy then use TO_DATE to convert it to a DATE data type and then TO_CHAR to display it to the format you want.

SELECT TO_CHAR( TO_DATE( string_value, 'dd.mm.yyyy' ), 'mm/dd/yyyy' )
FROM   table_name

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Oracle stores dates using an internal format. If you have a string that represents a date, you should probably fix your data model, so the column has the correct data type.

In the meantime, you can convert it to a date using:

select to_date(col, 'dd.mm.yyyy')

You can convert this back to a string:

select to_char(to_date(col, 'dd.mm.yyyy'), 'mm/dd/yyyy')

Upvotes: 2

Related Questions