Toad World® Forums

Bad Format on Date Import

Hello,

I have another suggestion (Or maybe there is a setting I need to turn one). I think this will be an easy one, but correctly its causing a lot of issues for me.

I have a table in DB2 with a column defined as a varchar(120) but when I upload a spreadsheet its reformatting the date field as a time stamp with a bad formatting for db2 time stamp format.

Let me explain in greater detail.

Here is the Table DDL:

CREATE TABLE DateTEST (
ColA varchar(120)
)

Here is the spreadsheet:

[View:/cfs-file/__key/communityserver-discussions-components-files/88/Book1.xlsx:320:240]

I am using the import wizard (which I Love and its a great you can create templates and just fire those templates off whenever you want).

Here is screen one of the wizard:

As you can see its keeping the format of Date. Now when we go the next screen its re-formatting the data to this:

Here is the results in the table:

ImportResults.png

The problem with this format is DB2 can't cast it as a date as I need it in the format of date when I push to final table.

When you run this SQL: Select cast(cola as date) from DateTEST

You get this error: Lookup Error - DB2 Database Error: ERROR [22007] [IBM][DB2] SQL0181N The string representation of a datetime value is out of range.

My suggestion is can you keep it as a date field and not convert the value to a time stamp? Or better yet can you rather format the time stamp as what is stated in the settings?

Such as this:

I know Dates and Time stamps are hard to use in Db2 for z/OS if they are not formatted correctly.

Thanks again for bringing a great product to us.

Toad for DB2 Beta 6.3.0.57 (64 bit)

Robert,

Thanks for the detailed information - we are investigating this now and we will keep you informed of our progress…

Jeff

Hi,

I found a similar problem doing compare to Excel or CSV after exporting to Excel or CSV. All date fields showed as different because of the different format that the dates were written to.

I agree with Robert that dates are hard to use in DB2 as the environment sometimes writes the date in a different format than DB2.

If I would have the time (or if I would be desperate :slight_smile: ) I would try to play with the system settings so that they would be similar to the format of DB2. I think there is no way to force Excel to save a date in a Format other than the system default, at least not using the regular API of Excel. I’m not an Excel expert but I tried playing with the settings a while back and this is what I remember.

Sincerely,

Shimon

Robert,

Today - you can import your values from your spreadsheet into an existing table with cola defined as DATE or within the import wizard - specify a new table with DATE as the column type

Wondering if that would work for you - or is there something special that you really need a varchar column???

Hello Jeff,

Sadly that wont help me.

This is the situation I am running into. We have a table in production which has like 20 columns all varchar(120). We are given an spreadsheet with various columns and various types of data. We use this spreadsheet to update values in a database. I can’t not change the DDL of the table since its in production and its made as varchar(120) to be flexible for any data type.

Does this help the issue I am running into?

Robert,

In Toad when you are exporting the timestamp values from a column that is a DB2 Timestanp to excel, you can format those columns like shown below (custom format: yyyy-MM-dd)

That will create values in your spreadsheet that can then be imported into your varchar columns with your expected values.

Please give that a try.

Hope that helps.

Jeff

Hello Jeff,

I think there is some miss communication. Sorry about that.

I am trying to import into DB2 and not export. I am trying to import a excel file into Db2 table. So the export functionality is not helpful in my case.

Does this help? Sorry for any confusion I have caused.

Robert

I just brought up the export wizard process in case you are creating your excel file via the Toad Export wizard.

Using my described format, it would then solve your issue when that file is used in the import wizard.

Sure. I am getting the file from the business. They just create random spreadsheets with data that needs to be updated.

Robert,

I ran this past our Import/Export Wizard expert and this is the feedback:

The datetime value from Excel is essentially a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt. User can see it when change the format.

We don’t support custom format when the datetime value is converted to string. I will suggest the user import the file into a temp table first with correct datatype and do a select to import the data with any casting needed to get to the right format.

– That is sort of what I stated in a previous post too. So at this time unless we probably will not consider to implement a change for this.

Jeff