Skip to Main Content
  • Questions
  • SQL Developer extract to Excel Date Format

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, valerie.

Asked: May 13, 2021 - 6:27 pm UTC

Last updated: May 18, 2021 - 3:50 pm UTC

Version: 20.4

Viewed 100+ times

You Asked

SQL Developer extract to Excel - Date Format is changing from 02-NOV-2020 12:00:00 AM to 11/2/20

Hi I'm using SQL Developer 20.4 and Excel 365 16.0.13127.21490.

When I run a SQL with dates, the dates are delivered in the format of 02-NOV-2020 12:00:00 AM. When I export to Excel, the excel file is showing the dates as 11/2/20. How do I go about keeping the original date format that Excel had?


and we said...

I'm not sure what you are asking here.

SQL Developer will export dates as excel dates, and then the Custom format specification (in Excel) can be used to format the dates however you choose.

For example, here's what came out of SQL Dev for ALL_OBJECTS

SQLDEV_EXPORT_DATE

Rating

  (3 ratings)

Comments

Clarification

valerie Amstutz, May 14, 2021 - 12:57 pm UTC

I need the exported Sql developer dates to remain in the same format that they are displayed in SQL when exported to Excel. For example if SQL Developer shows it as 02-OCT-2020 8:55:00 AM - that is how the date needs to be displayed in Excel as well. I have thousands of dates and cannot format them once in Excel... They need to come over and stay in the original format. Currently they are converting over to 10/02/20. Does that help clarify my problem? Is there a SQL Developer option to enforce formatting on export? Or is there an Excel or System setting that I need to set to keep the original 02-OCT-2020 8:55:00 AM format?
Chris Saxon
May 17, 2021 - 12:27 pm UTC

As the comment below says, Excel has its own defaults for displaying dates. You can change by setting the format in Excel itself.

Excel Date format

Sunny, May 15, 2021 - 10:54 am UTC

Hello valerie,
If you want 'dd-mon-yyyy hh24:mi:ss' in excel then on same machine you need to change system date format from 'mm-dd-yyyy' to 'dd-mm-yyyy'.

Microsoft excel by defaults picks date and time format from system's setting.
so do this and check work or not?
Have any doubts?

Convert to text using to_char

Evan, May 17, 2021 - 7:29 pm UTC

From what I understand, if the column is a date datatype, Excel will treat it as a date and auto apply date formatting. To get around this problem, format the date column using to_char and Excel will treat it as a text column.

I tried this on my computer and the date formatted using to_char displayed in Excel without any changes.
SELECT sysdate DateCol
, to_char(sysdate, 'DD-MON-YYYY HH:MI:SS AM') DateColFormatted
FROM dual

Chris Saxon
May 18, 2021 - 3:50 pm UTC

Yes, that can help, though it depends on your export format. If you use CSV then Excel can still display the dates in its own format.