Toad for Oracle 11.6.1.6 export to excel instance date format problem

Toad for Oracle
1

Hi all,
in Toad for Oracle 11.6.1.6 I execute this query:

select to_date(‘31.03.2013’,‘dd.mm.yyyy’) date_value
from dual

In data grid I get date_value: 31.3.2013.

Then I go to “Export Dataset” and choose export format “Excel instance”.
I uncheck all the checkboxes, press OK and get a new sheet in my currently open Excel 2010 file (xlsx).
The problem is that my date_value is now formatted like this:
31-oujak-2013

(“oujak” is the name for the month of March in croatian language)
This format does not correspond to any date formats that I have set in my windows control panel.
Excel treats it as a string, not as a date, and I can’t do any date operations on this value in excel.
I would like to get a date value in excel: 31.3.2013

I’ve also tried exporting it to an older version of Excel file (xls).
I’ve tried changing date format in windows control panel.
Nothing helped.

This used to work fine in Toad 10.

As far as I can see there is no option in Toad to set the date format when exporting to Excel instance.
When exporting to Excel File there is an option to set the date format, and it works fine, but exporting to a File is not good for me for some other reasons (strings which contain only numbers are are converted to numbers in Excel and I don’t want that to happen).

Is there a way to get the correct date format in Excel instance export in Toad 11.6?

Any help is appreciated.
Thanks!
Dalibor

2

Hello,

I have changed my laptop and switched to toad 11.6.1.6 x64 from 10.0.0.41 x86 and experience exactly the same behaviour when using export as excel instance. This is pretty much frustrating because date column is created as ‘General’ in Excel and creating chart does not work correctly ‘out of the box’. Does anyone have s solution? As Dalibor says - it was find in toad 10 :frowning:

3

Please look in this example…this is fully your session problem values.

SQL> select parameter, value from nls_session_parameters where parameter=‘NLS_DATE_FORMAT’;
PARAMETER VALUE

NLS_DATE_FORMAT DD.MM.RR

SQL> select to_date(‘31.03.2013’,‘dd.mm.yyyy’) date_value from dual;
DATE_VAL

31.03.13

SQL> alter session set nls_date_format=‘dd.mm.yyyy’;
Session altered.

SQL> select to_date(‘31.03.2013’,‘dd.mm.yyyy’) date_value from dual;
DATE_VALUE

31.03.2013

SQL>

Brg
Damir

4

Hey Damir,

Thanks for your input. That might in fact be a problem but I`m not sure if it really is.

My NLS_LANG defined at DB is ‘AMERICAN’ so my NLS_DATE_FORMAT did define format with MON.

I`ve set up NLS_DATE_FORMAT env variable to override it:

$ set|grep NLS

NLS_DATE_FORMAT=‘DD-MM-YYYY HH24:MI:SS’

NLS_TIMESTAMP_FORMAT=‘DD-MM-YYYY HH24:MI:SSXFF’

NLS_TIMESTAMP_TZ_FORMAT=‘DD-MM-YYYY HH24:MI:SSXFF TZR’

NLS_TIME_FORMAT=HH24:MI:SSXFF

NLS_TIME_TZ_FORMAT=‘HH24:MI:SSXFF TZR’

After that I relogged OS session and reconnected to DB.

Using SLQPlus I queried NLS_DATE_FORMAT:

SQL> select parameter, value from nls_session_parameters where parameter=‘NLS_DATE_FORMAT’;

PARAMETER VALUE

NLS_DATE_FORMAT DD-MM-YYYY HH24:MI:SS

SQL> select sysdate from dual;

SYSDATE

01-12-2014 09:49:16

Using TOAD I queried NLS_DATE_FORMAT

select parameter, value from nls_session_parameters where parameter=‘NLS_DATE_FORMAT’;

NLS_DATE_FORMAT DD-MM-YYYY HH24:MI:SS

select sysdate from dual;

01-12-2014 09:49:37

So up to now everything looks just as I want.

Unfortunatelly exporting to excel still produces date in full month name format:

SYSDATE

01-grudzień-2014 09:49:37

:frowning:

5

It has nothing to do with windows settings or NLS date format. We are exporting as dd-mmmm-yyyy hh:mm:ss. I will investigate the possibility of making this customizable.

6

When we send dates to excel, we can send them as dates or strings. We have had reports in the past of Excel getting Days and Months mixed up when we used to send as dates, so now we send as strings. Ideally, Excel should recognize that the string is a date, convert it, and then you should be able to format it how you like. Toad 10 sent the string as DD-MMM-YYYY, which worked great except for Slovakian. So then we changed to MMMM (which is month fully spelled out).

The next beta will have an option so you can tell Toad to export month as 3 letter month or fully spelled out. One or the other will hopefully work for everyone, and then the actual date format can be set in Excel.

As a workaround, for those with older versions of Toad, try the Excel File format.

7

AFAIK, you can always reproduce date as string in a way:

select chr(39) || to_char(sysdate, ‘dd.mm.yyyy hh24:mi:ss’) from dual;

And later transform those columns to custom date format in one command (vb script).

Brg,
Damir