Writing to DB2 SQL Dates & DateTime Error

Question

I am trying to write to a DB2 table but am receiving the below error.

"The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use."

All of the columns are either strings, integers, dates, or datetimes. The strings and integers seem to be formatted to the correct size. The dates and datetimes seem to be locked at too large of a size within Alteryx .

For example, in Alteryx, the dates are size 10 and the DB2 DATE is formatted to a size 4. Also, in Alteryx, the datetime is size 19 and the DB2 DATETIME is formatted to a size 6.

How do I force the formatting of the date and datetime to size 4 & 6 to be able to write to a DB2 table?

jdminton · Answer

Can you share the workflow? No need to include the data. If you are pulling a datetime field from SQL Server, are you sure there is a time included (depending on dataset, sometimes time is excluded from certain fields like a posting date).

Brian_Stoffel · Answer

@jdminton - Thanks. I narrowed it down to the DATETIME column that is throwing the error. The workflow simply goes from a MS SQL table and loads a DB2 table. The column names are exactly the same and all of the other columns are within the proper size.

The workaround is to format the DATETIME columns as DATE in Alteryx and then the DB2 table accepts the data. The problem is that the DATETIME stamp in the DB2 table is 12:00am, which we would rather have the actual DATETIME.

jdminton · Answer

It's a bit hard to diagnose without a workflow, but based on what you shared, I would identify where the date field is getting locked. If you are referring to it being locked in a formula tool, that is because you cannot change the format of an existing field in that tool unless you make it into a new column. If you are using in-DB tools, that would be controlled by your DB itself.

If all else fails, you may try converting the date to a string early in the workflow and converting to a date either just before or in the DB itself.

Good luck!