DateTime issue

One Star

DateTime issue

Hi,
I'm reading data through tMysqlInput and storing it as it is in a csv file. The table has a DateTime column and it sometimes has a value of '0000-00-00 00:00:00' but this value is stored in csv as '0002-12-31 00:00:00'. I'm also attaching tMysqlInput table schema. any idea?
One Star

Re: DateTime issue

also attaching the schema of tFileOutputDelimited.
regards,
One Star

Re: DateTime issue

Hi abdul_rehman,
Do you have null values in the data that you're reading? Maybe it is just giving the default value to the nulls.
One Star

Re: DateTime issue

Hi Khor,
the source table has '0000-00-00 00:00:00' which are stored as '0002-12-31 00:00:00', and you can also see that the default value of the column is set to '0000-00-00 00:00:00'.
One Star

Re: DateTime issue

Hi,
Maybe you should test the date in a tmap. Which version of Talend do you use?
One Star

Re: DateTime issue

Hi Or3l,
There is a tMap but i'm not doing any transformation there. And i'm using 4.2.2.
regards,
One Star

Re: DateTime issue

Hi,
also tested the date column in tMap and entered '0000-00-00 00:00:00' in date column value and tested it. The output was '0000-00-00 00:00:00'.
One Star

Re: DateTime issue

Hi,
any idea guys? or i have to deal with this with a case statement in LDR script?
regards,
One Star

Re: DateTime issue

Hi,
I've gotten the exact same thing.
Instead of 0000-00-00 00:00:00
I get '0002-12-31 00:00:00' when data is pulled into Talend.
My job pulls the data from a MySQL table.
When look at the table from MySQL Workbench it says correctly 0000-00-00 00:00:00
Any ideas?
One Star

Re: DateTime issue

It is caused because 0000-00-00 00:00:00 is not a valid date. It's a "bug" in MySQL. The first valid date should be 0001-01-01 00:00:00
there is never a day between the 31-12 and 1-1 by the way.
Community Manager

Re: DateTime issue

Hi
What's the expected result if the datetime is "0000-00-00 00:00:00" in Mysql table? Below is an expression used to set the column as null if the datetime is "0000-00-00 00:00:00" , on tMap:
row1.columnName.getTime()<=0?null:row1.columnName
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: DateTime issue

Has this issue been resolved? I added the parameter "noDatetimeStringSync=true&zeroDateTimeBehavior=convertToNull" to my MySQL connection as I saw in a few posts. When I use the querybuilder to query the data, it shows null. However, when I look at my data in a tLogRow, I'm seeing 31-12-0002. When I try to put this value into another database, it gives a error that says "Only dates in the AD era are accepted."
One Star

Re: DateTime issue

Test response.
One Star

Re: DateTime issue

Somebody's have fixed this problem ? 
Thank's