topic Re: DateTime issue in Talend Studio

DateTime issue

Anonymous — Sat, 16 Nov 2024 12:26:03 GMT

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?

Re: DateTime issue

Anonymous — Tue, 24 Jan 2012 07:59:15 GMT

also attaching the schema of tFileOutputDelimited.
regards,

Re: DateTime issue

Anonymous — Tue, 24 Jan 2012 08:30:04 GMT

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.

Re: DateTime issue

Anonymous — Tue, 24 Jan 2012 09:55:42 GMT

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'.

Re: DateTime issue

Anonymous — Tue, 24 Jan 2012 10:32:53 GMT

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

Re: DateTime issue

Anonymous — Tue, 24 Jan 2012 10:52:55 GMT

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

Re: DateTime issue

Anonymous — Tue, 24 Jan 2012 11:33:19 GMT

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'.

Re: DateTime issue

Anonymous — Wed, 25 Jan 2012 07:59:58 GMT

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

Re: DateTime issue

Anonymous — Wed, 22 Aug 2012 13:53:55 GMT

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?

Re: DateTime issue

Anonymous — Wed, 22 Aug 2012 14:05:40 GMT

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.

Re: DateTime issue

Anonymous — Wed, 22 Aug 2012 14:09:51 GMT

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

Re: DateTime issue

Anonymous — Mon, 27 Jan 2014 17:37:17 GMT

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."

Re: DateTime issue

Anonymous — Wed, 29 Jan 2014 16:44:34 GMT

Test response.

Re: DateTime issue

_AnonymousUser — Tue, 12 May 2015 10:43:42 GMT

Somebody's have fixed this problem ?
Thank's