Toad World® Forums

Inconsistent date column format in the "Data Grid" for exporting to csv

I need to "Export Dataset" from the "Data Grid" tab of Schema Browser but I'm getting inconsistent formats for the dates.

To test, I have a simple one date column table... in sqlplus
SQL> select col1 from pj.pjdates;
05/10/2020 00:00:00
05/10/2020 11:35:55

The data grid (and the exported csv) however, shows:
05/10/2020 11:35:55

I want to use the data as a source for sqlloader, so a consistent format is required.
Is there a way to always show/export the time portion, even if it's a trunc'd date?

(I know I can use TO_CHAR() but I want to use this data extraction technique for a large number of tables with a large number of DATE datatype columns so wanted an easier fix :slight_smile: )

Toad for Oracle version
View > Options > Data Grids > Data : Date format = dd/mm/yyyy
View > Options > Data Grids > Data : Time format = hh:mm:ss

Any ideas?
Thanks, Pat.

I'm pretty sure that there is/was an option to "always show times even when zero" or something along those lines, but was it in 12.12 I wonder?

If you go into the Toad Options screen again, there's a search field at the bottom. try using that to search for "time" or "times" or "zero" or "blank" or whatever else comes to mind.

Sorry, I'm miles away from a database and Toad at the moment.

Norm. [TeamT]

Try exporting to the SQL Loader format.

I just tested that in 12.12 and here are a couple of rows that it made:

7369;"SMITH";"CLERK";7902;"10/05/2020 15:43:09";800;NULL;20
7499;"ALLEN";"SALESMAN";7698;"02/20/1981 00:00:00";1600;300;30

Norm, I think you are thinking of
Export Dataset -> Excel File Format -> "Hide time portion if zero"

Thanks @JohnDorlon - that looks like it'll do the job! - I hadn't noticed the option in the dropdown.
Thanks @NormTeamT too.

Much appreciated, Pat.

Thanks John. I thought it was an option, for Toad in general, not for an export. Sorry, my mistake.

I'm getting old these days and stuff is aging out of the cache!