Toad World® Forums

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

Hi,
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;
COL1
05/10/2020 00:00:00
05/10/2020 11:35:55

The data grid (and the exported csv) however, shows:
05/10/2020
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 12.12.0.39
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.

Cheers,
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!

Cheers,
Norm.[TeamT]