CODEWITHSUNDEEP
sqloracle-database
Rajiv A
Rajiv A

Reputation: 943

Convert date format in Oracle

I have a date format 2011-01-06T06:30:10Z in Excel.I want to just load the date part into a table from excel.How do I get the date part from it. i.e. 2011-01-06

Thanks

Upvotes: 0

Views: 118

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

I think, some more explanation is needed.

Loading data into database is one part, and displaying it after fetching is another part.

If you have loaded the data into database, then all you need to do is use TRUNC. It will truncate the time portion and will display only the date portion.

A DATE always has a datetime part together. TIMESTAMP is an extension to the DATE type. And what you see the date looks like is not the way it is stored in database. The format is for we human beings to understand. A date is stored in 7 byte in internal format.

More information Based on OP's question via comments

NEVER store a DATE as VARCHAR2 datatype. A date is not a string literal. Oracle provides lot of FORMAT MODELS to display the datetime the way you want. Sooner or later, you will run into performance issues due to data conversion. Always use explicit conversion to convert a literal to a perfect DATE to compare it with other date value.

Upvotes: 0

neshkeev
neshkeev

Reputation: 6476

Try this:

select cast(TO_TIMESTAMP_TZ(REPLACE('2011-01-06T06:30:10Z', 'T', ''), 'YYYY-MM-DD HH:MI:SS TZH:TZM') as date) from dual

Upvotes: 2

Related Questions