0

I have a excel file that automatically imports data from an Oracle DB into the sheet. Everything works fine, but date formatting is wrong. In the Oracle DB, the format is 01-JAN-2016. But when it exports to Excel, the format suddenly changes to 1/1/2016.

Is there a good solution to this? Setting the cell format beforehand doesn't work. Once the data is imported, Excel automatically switches format to Date. I have tried a lot but can not solve this issue so I am wondering if any of you have ideas as to what to do. Thanks.

Improve this question
4
  • how are you exporting the data from the database to your sheet? Commented Jun 14, 2019 at 15:29
  • Select * from & table then CopyFromRecordSet Commented Jun 14, 2019 at 15:33
  • I'm trying to figure out if you're using sql developer or not - you have it tagged, but it sounds like you have an odbc linked data source in excel Commented Jun 14, 2019 at 15:39
  • Consider connecting with Get & Transform (aka PowerQuery) Commented Jun 14, 2019 at 16:59

1 Answer 1

Reset to default
1

The date isn't stored in "01-JAN-2016" format, that's just a representation of the underlying date value.

The recordset contains the data, not its representation. Format the column with a NumberFormat like dd-mmm-yyyy, and 2016/01/01 will look like 01-JAN-2016.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Excel will automatically switch NumberFormat to Date. I have done some coding to get it as "01-Jan-2016" but even that won't upload back into the database, which is where my issue begins. You can't UCase a date format. Converting the date into a string formatted as dd-MMM-yyyy seems to be a good option to solve this issue, but I have no idea how to do that.
@tokyo_north if Excel needs to see the date as a date (extremely likely if the query is a data sheet in Excel, that the worksheet needs to use for its purposes), the last thing you want is a string that looks like a date and its NumberFormat is essentially irrelevant. No idea what "won't upload back into the database" means, this is a SELECT query, data is coming from the database, not going to it. You can always add another column and do =UPPER(TEXT(A2,"dd-mmm-yyyy")), where A2 is your actual-date-value cell - seems Excel still understands that as being a date.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.