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?
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
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
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.