Toad misinterprets Date according the date format chosen in the Tools -> Options

Toad for IBM DB2
1

Hi,

I exported data from DB2 to excel or CSV and then imported it after altering some columns. The DATA included dates in the DD/MM/YYYY format. The month and day got reversed in the process.

This might be my fault, as maybe I should have checked how the data was presented in excel. I expect the date to stay correct irrelevant of what format I choose.

What I found more strange is that when clicking on the arrow next to the date in the grid, the date showed wrong if I checked the “Use local-independent format” checkbox in Tools - Options -> Environment -> Grid.

Can anyone using non-US date formats verify this?

Sincerely,

Shimon J.

2

I have not been able to recreate this.

Can you send me the DDL for the table you are using and an export file with some of that table’s data.

When exporting can you run a select from each column and also include casting to CHAR your date columns such as:

SELECT COL1, COL2, COL3, CHAR(COL3) AS CHAR_COL3 FROM TABLE

Also send your xls file that was created in Toad’s Export wizard.

What version of Toad are you running?

What version\platform of DB2?

If this gets complex or too cumbersome - I recommend opening up a support ticket.

Thanks - Jeff

3

I still see toad misrepresenting timestamps… example 6pm

Instead of 2016-04-27-18.00.00.000000
It displays it as 2016-04-27-06.00.00.000000

This makes it look like 6 am… but since timestamp does not show PM/AM then you cant tell what the correct time is.

This is in 6.0 and i believe was still issue in 6.2 Beta.

I filed a ticket on this 8 months ago.

Thanks,
Herb Vogel | Product Manager
479.361.7665 (O) | 479.466.6383 © | 479.361.7973 (F)
J.B. Hunt Transport, Inc. | What’s your nextmove?™
Single Source | Intermodal | Dedicated | Final Mile | Truckload | LTL | Refrigerated | Flatbed

From: “Jeff Podlasek” bounce-Jeff_Podlasek@toadworld.com
To: toaddb2@toadworld.com,
Date: 04/28/2016 03:22 PM
Subject: RE: [Toad for IBM DB2 - Discussion Forum] Toad misinterprets Date according the date format chosen in the Tools -> Options

RE: Toad misinterprets Date according the date format chosen in the Tools -> Options
Reply by Jeff Podlasek
I have not been able to recreate this.

Can you send me the DDL for the table you are using and an export file with some of that table’s data.

When exporting can you run a select from each column and also include casting to CHAR your date columns such as:

SELECT COL1, COL2, COL3, CHAR(COL3) AS CHAR_COL3 FROM TABLE

Also send your xls file that was created in Toad’s Export wizard.

What version of Toad are you running?

What version\platform of DB2?

If this gets complex or too cumbersome - I recommend opening up a support ticket.

Thanks - Jeff

To reply, please reply-all to this email.

Stop receiving emailson this subject.
Or Unsubscribe from Toad for IBM DB2 - Generalnotifications altogether.
Toad for IBM DB2 - Discussion Forum

Flagthis post as spam/abuse.

Email from J.B. Hunt Transport, Inc. is considered confidential and for use by the intended recipient or entity only. Any unauthorized distribution of emails, attachments, or information therein is strictly prohibited.

4

Go to Tools>Options>Environment>Grid

Look at Data Type Formatting and the DateTime tab. What is it set to? Are any of the two check boxes checked?

I have not been able to reproduce the issue that Shimon or Herb has talked about.

I would try restoring the defaults in the Options.

Thanks,

Eric Sheridan

Edit: Toad for DB2 6.1.0.134

5

what is the ticket number that was file? I would like to look it up.

6

Hi Eric,

Did you try this on a operating system configured for DD/YY/YYYY as the dafault date format?

Sorry for the delay in getting back to this post.

7

Hi Jeff,

I’ve been extremly busy since the Passover holidays, so I didn’t get a chance to get back and check on the post.

I hope to get the DDL and excel to you within a week.

Sincerely,

Shimon

8

I tried to reproduce the problem I had, but was not succesfull doing so.

The issue of the grid showing the wrong date when choosing the calendar dropdown to choose a date still exists, but is not so bothersome.