Trouble loading date using talend

[Anonymous]

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.

[manodwhb]

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

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

[Anonymous]

Thank you for your reply Manohar.

When I tried doing the same I got the below error

Please let me k ow 

[akumar2301]

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.

[Anonymous]

@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 🙂

[manodwhb]

@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

[Anonymous]

@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 🙂

[Anonymous]

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

[Anonymous]

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 🙂

[Anonymous]

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.