Am loading data from as400 to snowflake using talend, there is a varchar column which stores date in yyyymmdd format, when i load the data into snowflake, data is getting changed, for ex 19890501 value in as400 is loading as 19890500 in snowflake, i tried keeping varchar, date and number data types in snowflake, but still same issue, anyone faced the same issues pls help me out.
2 Answers
0
votes
This sounds like a classic time zone conversion issue. 6 hours back or forth can push off a timestamp by a day. For example, what you have as "20190102" might really be treated as "20190101 00:00:00" internally within the database, which would push back to 20190101 if any conversions happen as the data is getting exported. I've had similar issues with other older databases which I imagine are similar to as400.
Note, this can happen in some platforms even if you're saving it as a varchar. Sometimes these systems will try to display the date to you using your account's time zone, but they'll store it as UTC internally. So when converted to varchar, it might be saved as UTC.
One workaround is to "force" the timestamp to be a pure date, rather than a timestamp, by slicing off the YYYYMMMDD section of the data, essentially hard-coding it into a string until it gets into Snowflake.
There's no magic shortcut, though. It takes a bit of troubleshooting to see if this is truly your issue, and if so, how to handle it best.
0
votes
I would double-check the timezone between your original database and Snowflake and then try align it on the account level - this should be doable pretty easy:
https://docs.snowflake.com/en/sql-reference/parameters.html#timezone
