Toad World® Forums

Export to Excel instance treats dates as numbers


#1

Howdy!

In 12.7.0.48 64-bit, if I have any query with a DATE type, like this:

SELECT LOGOFF_TIME
FROM DBA_AUDIT_TRAIL
WHERE LOGOFF_TIME IS NOT NULL;

…exporting the dataset to an Excel 2010 instance, Excel shows the dates as numbers. Exporting to an Excel file works as expected.

I’m on Win7 SP1 64-bit.

I just checked 12.6GA (12.6.0.53) and get the same results…possible operator error (aka “PEBKAC” and “ID-10-T”)?

TIA!
Rich


#2

I just tried it and it’s working fine for me with the same version of Excel and Toad. We send all data types to excel as a variant, except dates, which we send as string. If I send dates as variants, excel does exactly what you just described, it treats them as numbers. Can you change the format in excel to make them appear as dates? I’ve never heard of Excel doing this. Another thing you can play with is the “Send month as” option in the export dialog of the beta.


#3

OK, I just tried v11.6 and it’s doing the same thing. I have a hard time believing this is the first time I’ve noticed this in the two years since that was installed, and I know my “upgrade” of Excel from 2003 to 2010 happened in the last year.

I’m leaning towards a Microsoft Orifice issue here…

Rich

Disclaimer: I’m putting the “beef” in “befuddled”.


#4

Oh yeah, I forgot to say that I did mess with the month option, as well as “Treat string fields as strings”, in all the combinations of the two, but no difference…

No big whoop really, as I’ve already been using the workaround forever. I only use the “instance” option when I really don’t care about the output and want something quick and/or dirty. Saving as a file is darned near as quick, but much less dirty.

Since this works for you, and I’m the first/only to notice this, let me officially rescind this post and have y’all working on something a whole lot cooler.

Thanks as always, John!

Rich

Disclaimer: There’s too much blood in my caffeine stream this AM.