Page 1 of 1
Problem while loading date into a table
Posted: Tue May 31, 2005 6:04 am
by Ramendra
I am facing one problem,
I have hardcoded date vlaue as "1900-01-01" in transformer and loading the same into a table. In
table it is getting converted into "12/31/1899" i.e one day less.
I am using DB2 API stage and in transformer I have coverted the date into StringToDate("2005-01-01","%yyyy-%mm-%dd") .
Any help on this would be appreciated
Thanks!
Ramendra
Posted: Tue May 31, 2005 6:46 am
by chulett
The Server version of the DB2 stage wants dates in internal format. I wonder if the same if true for the EE version?
one day less
Posted: Wed Jun 28, 2006 10:06 am
by cnguyen
I am experiencing the same issue and wondering if there is an explanation. I have the following PX job:
seq --> xfm --> DB2/UDB API
There is a date field in the incomoing data with YYYY-MM-DD format. The coressponding field in the DB2 database is of type Date. In the tranform stage, I am using the StringToDate function to convert to date data.
The value of the date field in the DB2 table is always one day less.
Thanks
Re: one day less
Posted: Wed Jun 28, 2006 4:00 pm
by sud
cnguyen wrote:I am experiencing the same issue and wondering if there is an explanation. I have the following PX job:
seq --> xfm --> DB2/UDB API
There is a date field in the incomoing data with YYYY-MM-DD format. The coressponding field in the DB2 database is of type Date. In the tranform stage, I am using the StringToDate function to convert to date data.
The value of the date field in the DB2 table is always one day less.
Thanks
Instead of writing to a db2/udb write to a peek and see if you are getting the correct result. This is very weird, unless DS has a HUGE bug in the DB2 stage.
The TZ environment variable needs to be set to GMT
Posted: Wed Jun 28, 2006 5:08 pm
by cnguyen
Technical Support acknowledged that this is a known issue. The workaround was to set TZ=GMT. Once this was set, the correct date was inserted into the Db2 table.
Thanks