Try as I might, I cannot get a date field to export as MM/DD/YYYY
Yes, I know I can use to_char(DATE,‘MM/DD/YYYY’) but this has a complication - dates exported using this method aren’t sortable correctly by Excel (Excel treats them as text, instead of dates, and thus doesn’t sort correctly if you have multiple years, as it only sorts on the first 2 numbers).
I’ve tried using TRUNC but TDA still exports the date like: MM/DD/YYYY 12:00:00 AM - my understanding was that TRUNC is supposed to drop everything BUT the date - obviously it’s not working that way.
One thing you can do is convert the Oracle date to a number in the internal Excel date format. When you export it to Excel, it looks like a number, so you would have to select the cells and apply an Excel date format to them. Then the cells would be true “Excel dates”.
example: I have an Oracle table with a column called MYDATE. I want to ignore the time portion of the date, including only the YYYY/MM/DD portion of the date. I can convert it to an internal Excel date with this:
select
to_number (to_char (trunc (MYDATE), ‘J’)) - to_number (to_char (date ‘1900-01-01’, ‘J’)) + 2
as excel_date
from MYTABLE ;
Does that apply to the export that occurs during Automation? 90% of my reports eventually move to automation once they’ve been fine-tuned, and I’ve found that the Excel export in automation (Select-to-File, in particular) doesn’t seem to follow the same rules as either the One-Click Export to Excel Instance or One-Click Export to Excel file.
What happens to fields that actually do have a date and time, and I need to capture both?
Debbie Peabody wrote:
Exporting dates to Excel uses the date format shown in the options screen of the prior post.
The Quick Export to Excel Instance uses Comm to export. The Export Wizard and Select to file in Automation use a different method. So you will see differnces on dates and numbers using these two methods.
If you want the display to allways display “DD/MM/YYY” then use the options. Auotmation will use this. But if you want to show time sometimes and dates only the other you will need to format in SQL.
Excel date fields always have the time embeded, it is usually suppressed because it isn’t part of the format you have selected.
I suspect that is why the TRUNC function is showing the times in those cells.
You can see this by playing with the custom formats in Excel
MM/DD/YYYY hh:mm:ss will show you 05/12/2012 12:00:00
and
MM/DD/YYY will only show you 05/12/2012, for the same value.
Think of it as hidden/displayed decimals. It only becomes a factor if you are doing date calculations involving fractions.
That make sense - I just wish there was a way to automatically get Excel to suppress the time when I do an export, but only for certain columns.
I suppose I could build a template file, but then I’m stuck creating macros and that’s a whole other issue with Excel 2010 and TDA.
Alan Thimot wrote:
Excel date fields always have the time embeded, it is usually suppressed because it isn’t part of the format you have selected.
I suspect that is why the TRUNC function is showing the times in those cells.
You can see this by playing with the custom formats in Excel
MM/DD/YYYY hh:mm:ss will show you 05/12/2012 12:00:00
and
MM/DD/YYY will only show you 05/12/2012, for the same value.
Think of it as hidden/displayed decimals. It only becomes a factor if you are doing date calculations involving fractions.
