Application Development Software

We aim to find an Excel format that matches as close as possible to what the date is formatted as in the result set.

Prior to 19.2, Excel exports did not have dated formatted as dates, period.

Are you using 19.2 or 19.4?

Please share a scenario with actual vs expected behavior. A select x from dual query for example...

Overall it is probably better to export date columns as such, but this also means dates before 1901-01-01 appear as -1 in excel. Something to be aware of.(and which i do not see in the 19.4 release notes)

SELECT LEVEL, DATE'1899-12-01' + LEVEL - 1 as day
FROM dual    
CONNECT BY LEVEL <= 62

Sadly when claiming millions of users, *any* such little change (even meant for the better) is likely to aggravate someone, somewhere.

To the OP: you can either apply the wanted format in excel or use an explicit TO_CHAR on the date columns.

It would be in the 19.2 release notes

Is this going to be corrected. It is still an issue with the latest release 20.4.1.407
The export date format should
Use the NLS_DATE_FORMAT specified in SQL Developer
Use the Windows Short date time format
Provide in the export preferences a format you with to export wityh
In both my instances they are set to YYYY-MM-DD.

i've described how it's intended to be working today

you mentioned "We aim to find an Excel format that matches as close as possible to what the date is formatted as in the result set. Prior to 19.2, Excel exports did not have dated formatted as dates, period."
I understand this, but the format I am using in Windows / Excel is "YYYY-MM-DD". "DD-MM-YYYY" follows the international settings here in Canada but I have overridden that explicitly. Should SQL Developer not follow the short date / time format of windows, or allow a setting in the preferences under export?

I agree with @jabraga98 that it's quite hard to defend the argument that "M-D-YYYY" is, quote, "as close as possible" to "YYYY-MM-DD", especially since the latter looks like a perfect format for Excel.
On the proactive side,
if you don't intend to improve this "as close as possible" logic,
and you are unwilling to add a formatting option to export,
can you at least include an "export DATE as string (backward compatibility)" flag?
Note that, as of 19.4, timestamp as well as timestamp with time zone still export as string. The latter cannot really be interpreted as datetime by Excel so that's probably ok but it's pretty straightforward to convert a timestamp without time zone into an Excel datetime with format like "<datepart> <timepart><decimal_sep><number_of_zeroes_for_fraction>" e.g. "yyyy/mm/dd hh:mm:ss,000000"

can you at least include an "export DATE as string (backward compatibility)" flag?
In your query, instead of querying it as a date, use to_char, and use whatever date format you want. It'll go into excel as a string, like it did years ago when people were complaining they wanted dates put into excel as dates.
So we did that.
Now we have literally hundreds of permutations between dates in oracle vs dates in Excel to deal with. Like I said, we're doing a best effort. I can ask the dev to explicitely ask for your date format to be accomodated.
Or you can in Excel, tell it you're in the UK/Locale, and say you want YYYY-MM-DD vs DD/MM/YYYY.

my excel is uk/Canada with a specific override of YYYY-MM-DD.
it is the style.XML that sql developer creates that forces this format. Where it decides that I do not know since windows is YYYY-MM-DD.
the tool should have export options for dates / time stamps that allow the user to export in their desired format similar to what the NLS parameters allow now

we don't know what your excel is when we create the file. we create a cell formatted to type date and we try to set a date format similar to how we see it in your query results

I do not understand “we try to set a date format similar to how we see it in your query results”. If that were the case the exported format would be “YYYY-MM-DD” as that is the format shown in the query results window.
all other sql tools that allow exporting that I have used allow this since each DB stores the actual dates internally in the own proprietary format.

I believe we can agree that
there is a workaround so house is not burning
DEV is making a best effort and we should be thankful for that.
It's just frustrating to be unable to understand why things are so complicated. Wouldn't Excel just take the date field as a date if there were absolutely no numberformat on it? Without wading knee deep into specification, I assume you (DEV) have a good reason not to make assumptions.
Still, I just did an experiment, setting xf numFmt=14 which is supposed to be builtin format "dd-mm-yy", to see what happens. Lo and behold, Excel opened the hacked worksheet using the date format according to my locale (yyyy-mm-dd). So, for me, this builtin style looks more like "ShortDate" rather than hardwired format strings.
I wonder how the attached Excel sheet appears for various people having various regional settings in OS or spreadsheed software (not restricted to Windows and MS Excel)
ee.xlsx (3.92 KB)

I could invite you to ready our source code, except it's not open or available. We're using the a 3rd party Java component to build / read excel files called Apache POI.

Hi,
I installed SQLDeveloper version 21.2.0.187
on this page (https://www.oracle.com/tools/sqldev/sqldev-bugsfixed-212.html) I see:
31529165DATE FORMAT IN EXCEL OUTPUT FROM SQLDEVELOPER DOES NOT MATCH NLS SETTINGS

But when I export to XLSX I (still) always get this format: "m.d.yyyy h:mm AM/PM"
My NLS and local windows settings are both dd.mm.yyyy and would be really glad if SQL developer could export with such format (or even better if I could set my own default export format in SQL developer as some others suggested already).
Is this behavior still "as expected"? Reading the "bugs fixed" section I would assume that this was fixed or did I not understand that bugfix comment correctly?
Thanks!

Hi,
In version 21.2.1.204 (Build 204.1703) is the same problem:
When I export to Excel(xls, xlsx) I get this format: "m.d.yyyy h:mm AM/PM".
In my NLS and local Windows settings are both formats "yyyy-mm-dd" and "yyyy-mm-dd hh24:mi" (for long date) - all NLS is "POLISH".
The expected date format is defined in Preferences - NLS.
Can you fix it?
PS. If there is another place, special for the date format when exporting to excel file, please let me know.
Regards
Marek

I agree it’s quite a nuisance!
I generate several ad hoc reports every day with numerous date fields, and it’s tedious to go and correct the date formats afterwards.

What is even worse it that the stupid format “m-d-yyyy” is very dangerous.
When your NLS date format is something like “dd-mm-yyyy” you have a big risk that you will read the date in the wrong format.

If it is not possible to find an Excel format matching the NLS-format at least keep the order of the date elements.
If that is also to hard, then please use a format, where the month is nonnumeric such as “mmm-d-yyyy”

I agree that SQLDeveloper needs a setting somewhere rather than put a TO_CHAR around date columns. Also agree to the risk on having the month as a number as it causes confusion. There is already a tab for excel export settings so this seems an ideal place to add it.
SQLDeveloper 21.2.1.204 build 204.1703
Thanks in advance (hopefully)

Cheers
Nigel

In my personal case I have been reviewing all versions since 19.2 to check if the export date to excel has been fixed, taking into account the format that appears visually in sql developer in my case the format is DD-MM-YYYY both in NLS as the Windows date format, but when exporting it to excel it appears MM-DD-YYYY, the NLS format should simply be used to export it to excel, or failing that, create a property in setting where the format is indicated. you want to use; simply the format that they are using at the moment only benefits the community that uses the MM-DD-YYYY date in their countries, but they have left aside other users who do not use this type of date, I have also been giving this post follow-up to see if there has been any progress, as an additional comment I have been limited to continue using version 19.1 or 19.2 only because of the date issue; because in my case there are too many dates to be modifying it in all the already elaborated queries and it is not practical to be changing the format in excel, I have simply had to not take advantage of the new functionalities due to this limitation in the dates when they are exported to excel, and Of course I know that you can pass the date as you wish with TO_CHAR, but it is also not practical in my case.
So to finish I urge you to return to this issue and fix it once and for all in the next version, it is recommended that the tests be done with different machines that are not configured for the American language, and many users are proactively requesting this expected change of the date, I also congratulate the decision that the date in DATE format when exported to excel appears in DATE, it seems fine to me now I just hope that it is the same format as SQL DEVELOPER.

Best regards.

It's very interesting that a group wants a change and you can change it so easily without considering that the other users have been coming to terms with the previous situation for years and creating thousands of queries.
Before I have changed all of them, I prefer to spend a little time looking for another software that handles date formats better or is even parameterizable. Even if this should cost something, it would be a win-win situation.

I often hear product managers arguing about the hundreds or even thousands/millions of permutations - why use software? Because you want to make complicated or complex or hundreds of permutations simple(r) for the user - otherwise we just leave out everything complicated and go back to stone slabs and chisels ;)

Hi,
Release 21.2 states the following in the list of bugfixes:
31529165 DATE FORMAT IN EXCEL OUTPUT FROM SQLDEVELOPER DOES NOT MATCH NLS SETTINGS
I am currently using 22.2 and an excel export still uses the format M-D-YYYY, while I have my NLS preferences set to:
Anyone knows if I am doing something wrong ? or is this enhancement not working yet ?
Regards,
Pascal

Hi All,
I'm not an ORACLE or SQL Developer guru, but can I suggest something practical?
For those of you, who like me generate many, many Excel worksheets out of SQL Developer and find this "bug" infuriating, add the macro below (or something similar with your required date format) to your Excel set-up and when you open your exported Excel worksheet to tidy it up, as you no doubt do, just run this macro to reformat all dates instantly.
It's clunky I know, and some VBA wizard out there can improve it I'm sure, but it might help some of you until the issue is resolved "properly" and SQL Developer will take note of NLS date formats when exporting to Excel.
Enjoy :)

Sub ReformatDatesFromSQLDeveloper()
Application.ScreenUpdating = False
For Each wsc In ActiveSheet.UsedRange
If IsDate(wsc) Then
If InStr(1, wsc.Value, ":") > 0 Then
wsc.NumberFormat = "dd-mmm-yyyy hh:mm:ss"
Else
wsc.NumberFormat = "dd-mmm-yyyy"
End If
End If
Next
Application.ScreenUpdating = true
End Sub

Hi All,
perhaps not the best solution to a problem that persists for years and can be reproduced at any time, is to use macros.
May I ask when this problem will be solved?

NLS setting: YYYY-MM-DD
Windows short date format: YYYY. MM. DD.
Exported format in Excel: DD.MM.YYYY
Version 22.2.1.234
This version still contains this bug.

Thank you for your efforts.

It is incomprehensible to me that a problem persists for such long time. Updates should bring improvements and errors should be corrected, but this error still exists in the current version (23.1.0.097.1607 - April 19, 2023). What I don't quite understand is, the error didn't exist until version 19.2!

So why can't the correct procedure for excel export be taken back from an older version so that the NLS setting can also be used?

@ThatJeffSmith-Oracle: OK the third-party application "Apache POI" is used for the export of SQL-Developer - but can't you start there and carry out the correction?

Hoping for a response from Oracle support and thanks in advance for your efforts!

Miro

Previously we didn't format dates as dates in Excel and everyone complained they had to format their cells. So then we made the change to export dates as dates, but then it's ok how do we format the cell in terms of date display.

So ‘the error didn’t exist until 19.2' isn't an accurate statement.

Thank you for your reply. But it is only a comment and not a solution. As a user, I only see the result that the Excel export changes the dates against my formatting request (without having to know what the technical background is, because it is irrelevant to my result). And it means more effort for us, otherwise not so many users would put in the effort in this article to formulate their contributions. So once again the question: "When can we as end users expect the same export result as it was set in the configuration, without changing the days, months and years? That must actually be possible for such a large company; -))

Many thanks

Miro

It is what it is, as you see it today, and there are no plans to change it.

Either format it the way you want as a string with to_char() calls in your queries, or adjust the date formats as needed once it gets to Excel.

As large as the company is, we have one developer on this feature, who has 30 years experience, and dealing with the complexities of oracle dates/timestamps and Excel's date combined with what the java library that generates the Excel files, this is the best we could do, within reason.

This is my last comment on the subject.