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. 
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.
How can we get Toad to export SQL dates as date text in all cases without workarounds? Testing TDP 5.7 by resetting all options to default and running a quick export to CSV.
SELECT CURRENT_DATE, CURRENT_DATETIMESELECT CAST(CURRENT_TIMESTAMP AS DATE), CURRENT_TIMESTAMPSELECT Date(), Now()SELECT CURRENT_DATE, CURRENT_TIMESTAMPThe desired behavior is the same as this request from 2009: to export dates as dates in all cases, not as dates in some cases and as datetimes with 00:00:00 in others.
Can we change a setting somewhere that turns off this inconsistent behavior?
Thanks!