topic Re: Trouble loading date using talend in Talend Studio

Trouble loading date using talend

Anonymous — Thu, 11 Apr 2019 08:08:10 GMT

Hi,

I have a trouble while loading date of value '01-01-0001 00:00:00' and '01-01-4712 00:00:00' into my Database with Talend as the ETL tool. Talend is not recognizing the format as date but considering it a string. When I explicitly convert the string to date format(MM-dd-yyyy HH:mm:ss), the job building is popping out an error "Cannot convert value to date". Can anyone help me with this?

Thanks in advance,

Ramya.

Re: Trouble loading date using talend

manodwhb — Thu, 11 Apr 2019 08:30:27 GMT

@rchinta , you can string to date in below function in tmap ot tjavarow

TalendDate.parseDate("MM-dd-yyyy HH:mm:ss", yourStringData)

Re: Trouble loading date using talend

Anonymous — Thu, 11 Apr 2019 17:21:28 GMT

Thank you for your reply Manohar.

When I tried doing the same I got the below error

Please let me k ow

Re: Trouble loading date using talend

akumar2301 — Thu, 11 Apr 2019 19:58:30 GMT

Your input is already Date. It doesn’t need parsing.

And why you ate thinking that it is read as Date?

Screenshot of job could help.

Re: Trouble loading date using talend

Anonymous — Fri, 12 Apr 2019 05:07:19 GMT

@rchinta

Since your input value is already a date, your issue could be due to date range problem.

You are trying to load a date with year 4712 to database. But DBs have some limitations on the date range they can handle.

For example, Oracle can handle all the dates between 0001-01-01 to 9999-12-31. But Teradata's date range is different and it can handle only the date range up to 3500-12-31. So you need to check the maximum value of date allowed for your DB.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Trouble loading date using talend

manodwhb — Fri, 12 Apr 2019 05:40:52 GMT

@rchinta ,as part of testing can you insert the default value like 31-12-9999 ?

check the below link

https://community.teradata.com/t5/Database/Default-VAlue-for-a-Date-Column/td-p/42400

Re: Trouble loading date using talend

Anonymous — Fri, 12 Apr 2019 06:25:25 GMT

@manodwhb @rchinta

If you are using Teradata, the maximum value allowed for date is 3500-12-31 and not 9999-12-31. If you are using some other DB, please consult the corresponding maximum threshold value allowed from DB documentation.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Trouble loading date using talend

Anonymous — Fri, 12 Apr 2019 07:15:28 GMT

Thank you everyone for your helpful recommends.

When I tried giving year as 1990 it worked fine. I initially thought it is not considering as a date because it is throwing an error 'value to date'. I'm using Redshift Database. So, I guess I have to check for the date value range prior to my loads.

Talend Community have been very supportive

Re: Trouble loading date using talend

Anonymous — Fri, 12 Apr 2019 07:18:26 GMT

Perfect.

Could you please close the topic as you have got the answer for your query?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Trouble loading date using talend

Anonymous — Mon, 15 Apr 2019 07:23:34 GMT

Hi Team,

As mentioned I've tried loading data with year 0001 into Redshift directly. It got loaded into the Redshift DB without any issues.

So, I guess it may be an issue with Talend itself.

Can you guys please help me further.

Re: Trouble loading date using talend

Anonymous — Thu, 18 Apr 2019 09:30:29 GMT

Hi Ramya,

Could you please share your job screenshot and the component where you are mapping the date in correct format? End of day, Talend is a Java code generator. I could see that the dates have mentioned is getting converted from string to date properly in Talend tMap.

The function used is as below.

TalendDate.parseDate("dd-MM-yyyy", "01-01-4712")

If your data is already in date format, you can skip this conversion step and can directly map them.

I do not have a Redshift DB handy right now. So more screenshots of your overall flow will help us to analyse further.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Trouble loading date using talend

Anonymous — Wed, 24 Apr 2019 09:47:33 GMT

Hi Team,

Thank you for your suggestions.

My value is already a date. So, I reckon parsedate function doesnot work out.

I didn't perform any conversions. Also didn't use any tmap. Still, we are faing error converting value to date error.

Thanks,

Ramya.

Re: Trouble loading date using talend

manodwhb — Wed, 24 Apr 2019 10:06:53 GMT

@rchinta ,what error are you getting? if you are sure and want to convert one date format to another date format,just use tconverttype to convert the one date to other date or sting to date and let me know the error?