Toad World® Forums

Date Fields

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.

Any ideas?

How about this:
TO_DATE(TO_CHAR(TRUNC(SYSDATE), ‘DD-MON-YYYY’), ‘DD-MM-YYYY’)

That works great on SYSDATE, but not on any of my other fields. I think it’s because all of my other date fields are DATE type.

As soon as I convert back to a DATE type using TO_DATE it slaps the 12:00:00 AM on the output - very annoying - and TRUNC doesn’t drop it off.

acharest_031 wrote:

How about this:
TO_DATE(TO_CHAR(TRUNC(SYSDATE), ‘DD-MON-YYYY’), ‘DD-MM-YYYY’)

But what happens when you export it out to your excel sheet. Is the time stamp still there?

You can also do this. See the screenshot.

Exporting dates to Excel uses the date format shown in the options screen of the prior post.

Debbie

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 ;

Message was edited by: jacquesrk

I have a couple of questions about that:

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

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

Debbie

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.

Debbie

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.

Hope this helps.

A.

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.

Hope this helps.

A.