Toad World® Forums

Eliminating hh:mm:ss from text exports

In my previous version of Toad (9.0), I was able export query results into a tab delimited text file and date formatted fields would return as such: MM/DD/YYY.

In Toad for Data Analyst date fields return like this: MM/DD/YYYY hh:mm:ss.

I’ve looked everywhere. I’ve tried truncating the date, to no avail.

I’ve tried to_char, which works, but then the data type is varchar2.

I have a feeling I’m missing something simple and obvious.

Please help.

Thanks,

Scott

Did you try using the Oracle function TRUNC? I think that would get you what you need.

Debbie

I did. I noticed that the hours, minutes, and seconds becomes zeros.

I think I need to understand more about what you want to do with the exported Tab Delimited file.

Do you want to:

1)select from a table and truncate the timestamp
2) then import into a Date column?

If so, then using trunc is the correct way to do this even though the tab delimited file shows zeros.

Sample SQL:
SELECT EMPLOYEE_ID, TRUNC(TO_DATE(START_DATE,‘DD-MON-YY’)), TRUNC(TO_DATE(END_DATE,‘DD-MON-YY’)), JOB_ID, DEPARTMENT_ID
FROM HR.JOB_HISTORY;

Sample export:
102 ‘2093-01-13 00:00:00’ ‘2098-07-24 00:00:00’ ‘IT_PROG’ 60
101 ‘2089-09-21 00:00:00’ ‘2093-10-27 00:00:00’ ‘AC_ACCOUNT’ 110

This inserts and displays as default time of 12:00 AM.

Please clarify what the end result is that you want to accomplish.

Debbie

If you do not want the time to display in any grids you can change the date time display to ShortDate. To do this use Tools | Options | Environmnet | Grid | DateTime Format.

Debbie

I tried the trunc function as per your example. I’m still receiving a time stamp.

I already tried the short date grid option.

I think I’ll use the to_char function and live with the output being varchar2. It’s just Toad 9.0 does, so I assumed Toad for Data Analyst would be able to as well.

I appreciate your help. :slight_smile:

Thanks,

Scott

If you have time I would still like to understand what TDA needs to do to match the functionality in Toad for Oracle.

I would be happy to make any changes to the application that will benefit you and others.

Please feel free to contact be offline with sample files if you like.

Debbie
dpeabody@quest.com

Are you exporting into the same spreadsheet? I have seen this in the past when using Excel that Excel holds onto the cell formatting in the column even when the data is deleted on that worksheet. When you export into that worksheet even after using the trunc function Excel will format it as a date-time field.

If this is the problem, it is not a Toad issue, rather an Excel issue.

Hope this helps/

I have the same problem. My co-worker uses Toad for Oracle & I use Toad for Data Analysts. I couldn’t find a setting to change in Toad for Data Analysts to truncate the time upon export. So, I ended up just using a Date function in my sql script.

It seems to me that using to_char is suitable for this export.

select to_char(ORDER_DATE, ‘MM/DD/YYY’) from orders

Let me know if I am missing something on why an option is needed.

Debbie P.S. So far we have not had an option because it is more difficult to import dates that use various date formats. Sticking with one format is more consistant for us.