Copy to Excel - weirdness for old dates

[AmyG]

Hi,

I have a new version of PL/SQL Developer (v12) on a new laptop that has Excel Office 365 ProPlus. (Previously used PL/SQL Dev v9 and some earlier version of Excel). NOW when I have query results and "Copy to Excel", my low-dates (1/1/1800) show up as all ####### with a value (when I put my cursor in the cell) of -36522. They need to show up as 1/1/1800. I can't find a way to tell PL/SQL Developer to do this. Although when I Export Query Results (to an Excel file), it does show up as 1/1/1800, but it also adds two decimal places to all my integers fields, NOT helpful). The Copy to Excel is much more useful to me. I would like to get that fixed. Please advise.

 

[Marco Kalter]

This is indeed not correct. We'll check it out and will try to fix it.

The only suggestion that I have is to use the to_char() function to convert these old dates to text, so that they are also passed to Excel as text.

 

[Karthi S]

Question:

In preferences(version 11.0), Window Types --SQL window there are 2 preferences
1) Number fields to_char
2) Date fields to_char

will it be helpful to enable those 2 options instead of changing the SQL to have to_char()?

 

[Marco Kalter]

The "Date fields to_char" option will indeed also have the same effect as using the to_char() function.

It will work on all date fields of course, so you don't have the option to to specifically use the to_date() function for just the fields with very old date values.

 

[AmyG]

I can't add a preference of "Date fields to_char" because then 1/1/1800 shows up as 01-JAN-00...which is ambiguous with the 2-digit year. I can, in select cases, add a to_char in the query (where I can define the format); however, for some of my blanket testing, this would require thousands of date fields to be explicitly named and converted in the SELECT statement, vs. SELECT *. So, a more permanent solution is very desirable.

 

[AmyG]

PS Highlighting results and Copy with Header and pasting into Excel can also work in many cases...however, that has issues with dropping leading zeroes on different (non-date) fields. Such as 0123, when pasted, is turned to 123.