oracle: Column datatype in 'char' getting converted to date format while extracting to excel

Question

A column has values like '05-11' in char datatype. But when I export the records into excel file, it gets converted to 05-Nov. How to fix this issue in PL/SQL?

I tried the below code.code is the column name

Update query_table q set code = 
substr(q.code,0,instr(q.code,'-')-1)||'-'||substr(q.code,instr(q.code,'-')+1,2);

But this doesn't seem to be working. Please help if you have faced similar issue before?

APC · Accepted Answer · 2019-01-25 16:24:42Z

1

This is not a problem with data, this is Excel trying to be helpful. It's seeing a value of 05-11 and - thinking it recognises some form of date - renders it with the standard date format.

Possibly you can fixing Excel display defaults. But the safest approach would be to change the export so it wraps the column in double-quotes. Excel on seeing "05-11" will recognise it as a string and not mess with it. You can make this happen by configuring the export tool. For instance, if you're using the TOAD Export Wizard there should be an option (maybe a checkbox?) to apply double-quotes to exported values.

answered Jan 25, 2019 at 16:24

APC

147k20 gold badges180 silver badges289 bronze badges

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

2 Comments

ai03 Over a year ago

Hi..I have also one column in date datatype in this table. How can the excel differentiate column in char and column in date? is there anything I can do in the pl/sql coding part? I did extraction from the front end of application.

APC Over a year ago

If you want me to comment on your code you'll need to post a (redacted) example of it, and some sample data which illustrates the problem you're experiencing

Mark D Powell · Accepted Answer · 2019-01-25 16:53:38Z

0

aiswarya, try placing the value in double quotes "05-11" when you generate it.

answered Jan 25, 2019 at 16:53

Mark D Powell

1512 bronze badges

Collectives™ on Stack Overflow

oracle: Column datatype in 'char' getting converted to date format while extracting to excel

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related