Trouble loading date using talend

Five Stars

Trouble loading date using talend

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.

 

Forteen Stars

Re: Trouble loading date using talend

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

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

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Trouble loading date using talend

Thank you for your reply Manohar.

When I tried doing the same I got the below error

C16E400.PNG

Please let me k ow 

Eleven Stars

Re: Trouble loading date using talend

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.
Regards
Abhishek KUMAR
Employee

Re: Trouble loading date using talend

@Ramya_16 

 

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 :-)

Forteen Stars

Re: Trouble loading date using talend

@Ramya_16 ,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

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Employee

Re: Trouble loading date using talend

@manodwhb @Ramya_16 

 

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 :-)

Five Stars

Re: Trouble loading date using talend

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 supportiveSmiley Happy

 

Employee

Re: Trouble loading date using talend

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 :-)

Five Stars

Re: Trouble loading date using talend

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.

picture-0001.png

 

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

Can you guys please help me further.

Employee

Re: Trouble loading date using talend

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. 

image.png

 

image.png

 

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 :-)

Five Stars

Re: Trouble loading date using talend

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. Date_0001.PNG

 

Thanks, 

Ramya.

Forteen Stars

Re: Trouble loading date using talend

@Ramya_16 ,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?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog