Bug? - Export to Excel: date formatted as text

Danilo Piazzalunga

Hi,

when exporting query results to Excel (2003+, .xlsx format), DATE and TIMESTAMP columns are not formatted as dates; instead, they are formatted as text.

Tested versions:

• 18.1
• 18.2

Steps to reproduce:

1. Start up SQL Developer using the default settings
2. Run some query on a table or view containing DATEs and TIMESTAMPs. For example, the ALL_OBJECTS data dictionary view:

   • select * from all_objects;

3. Export query results to an Excel (2003+) .xlsx file
4. Open the resulting file

Expected result:

• CREATED and TIMESTAMP columns contain numbers formatted as dates. For example:
  • CREATED should contain 42746, formatted as a date (11-JAN-17)
  • TIMESTAMP should contain 42746.5327893519 formatted as a date (2017-01-11:12:47:13)

Actual result:

• CREATED and TIMESTAMP columns contain text. For example:
  • CREATED = "11-JAN-17"
  • TIMESTAMP = "2017-01-11:12:47:13"

Marwim

It's among the Top 5 requests for SQL Developer

https://apex.oracle.com/pls/apex/f?p=43135:7:::NO:RP,7:P7_ID:18381

thatJeffSmith-Oracle

we don't format the cells when we build the excel file, so you just need to select the column, ctrl+1, and set the date formatting as desired

B.Delmée

@ Danilo: xxx_OBJECTS is misleading as the column named "TIMESTAMP" is actually of VARCHAR2(19) type!

@Jeff: not sure what you're saying, i've been wondering before why sqldev did not export date columns as such to excel. It appears to me they are exported as text formatted according to one's NLS date mask - checked in 4.1 and 18.2

Marwim

It's among the Top 5 requests for SQL Developer

https://apex.oracle.com/pls/apex/f?p=43135:7:::NO:RP,7:P7_ID:18381