Convert date format in Oracle

Question

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

If you have loaded the data into database, then all you need to do is use TRUNC. A DATE always has a datetime part together. And what you see the date looks likr 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. — Lalit Kumar B
– Lalit Kumar B, Commented Oct 10, 2014 at 14:59
@lalit kumar B Thank you for this information.I will try to load the data ,but before loading I was thinking what column type I should create since it has Z at the end so was not sure if it's timestamp format — Rajiv A
– Rajiv A, Commented Oct 10, 2014 at 15:04
After you load it, use TRUNC. It will truncate the time part and will display only the date part. If this is a part of your requirement, let me know, I will add it as answer — Lalit Kumar B
– Lalit Kumar B, Commented Oct 10, 2014 at 15:10
And do NOT use PL/SQL tag for SQL questions. Both are different. — Lalit Kumar B
– Lalit Kumar B, Commented Oct 10, 2014 at 15:12
I get the trunc part, but my issue was loading "2011-01-06T06:30:10Z" value into a table with only date part.Or even if I have to load the data as is, I have to create a varchar2 type column. Correct? — Rajiv A
– Rajiv A, Commented Oct 10, 2014 at 15:18

neshkeev · Accepted Answer · 2014-10-10 14:53:06Z

2

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

answered Oct 10, 2014 at 14:53

neshkeev

6,4863 gold badges32 silver badges49 bronze badges

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

2 Comments

Wernfried Domscheit Over a year ago

You don't need the REPLACE, simply do cast(TO_TIMESTAMP_TZ('2011-01-06T06:30:10Z', 'YYYY-MM-DD"T"HH:MI:SS TZH:TZM') as date)

Rajiv A Over a year ago

@Wernfried Yep! that worked too. Thank you.It would be definitely help in quick processing

Lalit Kumar B · Accepted Answer · 2015-04-23 13:55:39Z

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.

Okay, I was talking about converting and loading the data, displaying is simple.I will now do something like this in excel, "=insert into test values (cast(TO_TIMESTAMP_TZ(REPLACE("&A2&", 'T', ''), 'YYYY-MM-DD HH:MI:SS TZH:TZM') as date));"
That is fine. Good luck! You can search google for But I want to store date like and you will get a very good article by Ed Stevens, read it. It is very good. I hope my answer gave you a better understanding :-)
@RajA This is article edstevensdba.wordpress.com/2011/04/07/nls_date_format

Collectives™ on Stack Overflow

Convert date format in Oracle

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related