Forum Stats

  • 3,837,280 Users
  • 2,262,245 Discussions
  • 7,900,245 Comments

Discussions

SQL Developer Excel Export Date Format not right

jabraga68
jabraga68 Member Posts: 6 Blue Ribbon
edited Jan 20, 2020 6:53AM in SQL Developer

since Oracle SQL developer 19.2 when exporting data to excel the date format does not match the NLS formatting in SQL Developer or the format in Windows.

I have set SQL Dev to be "YYYY-MM-DD HH24:MI:SS" and Windows is set to "YYYY-MM-DD" but when excel is exported the format is MM-DD-YYYY.  However exporting to Text or delimited honors the NLS parameters.

I know my excel is correct as if enter a date it must be in YYYY-MM-DD format as it should.

This used to work until verson 19.2

I have verified this issue on Windows 7 / 2008 / 10

I have also looked at the Style.xml that is created for the Excel doc and it clearly shows there M-D-YYYY as the format.  Where is this coming from?

«1

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,687 Employee
    edited Jan 19, 2020 4:25PM

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

  • B.Delmée
    B.Delmée Member Posts: 604 Silver Badge
    edited Jan 20, 2020 6:53AM

    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.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,687 Employee
    edited Jan 20, 2020 6:19AM

    It would be in the 19.2 release notes

  • jabraga68
    jabraga68 Member Posts: 6 Blue Ribbon

    Is this going to be corrected. It is still an issue with the latest release 20.4.1.407

    The export date format should

    1. Use the NLS_DATE_FORMAT specified in SQL Developer
    2. Use the Windows Short date time format
    3. Provide in the export preferences a format you with to export wityh

    In both my instances they are set to YYYY-MM-DD.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,687 Employee

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

  • jabraga68
    jabraga68 Member Posts: 6 Blue Ribbon

    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?

  • User_1M7LL
    User_1M7LL Member Posts: 2 Red Ribbon

    I agree with @jabraga68 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"

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,687 Employee
    • 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.

  • jabraga68
    jabraga68 Member Posts: 6 Blue Ribbon

    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

    User_BXD76
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,687 Employee

    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