[resolved] cannot convert string to date in tMap

One Star

[resolved] cannot convert string to date in tMap

Hi everyone.
I've the csv file in which one column has date (which is defined as Number in csv file) but in tFileInpuDelimited it read as string in which some of the vales are like 20140424 and some are like 2014-04-25 in the same column.I've to parse this data into output component(tOracleOutput)as date column.So I tried to convert the string to date by using 
TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row1.column1)) and changed the output column as date with format yyyy-MM-dd.
Then I'm getting error as 
Unparseable date: "20140424".
How could I parse this into output component?
Any help would be greatly appreciated.
Thank you in advance.

Accepted Solutions
Moderator

Re: [resolved] cannot convert string to date in tMap

Hi Shri_Kul1,
Please take a look at my screenshot.
Feel free to let us know if it is what you are looking for.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.

All Replies
Five Stars

Re: [resolved] cannot convert string to date in tMap

use below code.
TalendDate.parseDate("yyyyMMdd",row1.column1) 
One Star

Re: [resolved] cannot convert string to date in tMap

Thanks for your prompt reply umesh.
I tried even that also. But it didn't works. Would you suggest more ideas?
Can I write if else condition in tMap to take any value either yyyyMMdd format or yyyy-MM-dd format and then to insert into output column as date with yyyy-MM-dd format? If so How should I implement this?
Any help would be appreciated.
Thank you.
Five Stars

Re: [resolved] cannot convert string to date in tMap

output will not ask you for specific format, it will required only Date object, If you show me input string from your file then I can suggest you better.
One Star

Re: [resolved] cannot convert string to date in tMap

Thanks for your reply. The input column is as below.
Date
2014-12-21
20140214
2014-12-13
2014-12-23
20141201
The output should be in yyyy-MM-dd fromat only whatever may be the input format.
Five Stars

Re: [resolved] cannot convert string to date in tMap

So you have two formats in single column. then you can use below logic which help you. 

yourcolumn!=null && yourcolumn!=" "?
TalendDate.parseDate("yyyyMMdd', yourcolumn.replaceAll("-", "")):null

this will solve your problem.
One Star

Re: [resolved] cannot convert string to date in tMap

Thanks for your prompt reply.
Sorry Umesh I forgor to tell you onemore thing.
I also had -1,-2 values in that same column.That above problem is solved with TalendDate.parseDate("yyyyMMdd",row1.Column1).
Now the problem is with -1,-2 values. I've to place 0000-00-00 in output column when I get those values from input file.
How could I do this?
Thanks you
Five Stars

Re: [resolved] cannot convert string to date in tMap

You can do in the same way, you have to check the length of column if it is less than expected then replace with, desire string. 
One Star

Re: [resolved] cannot convert string to date in tMap

Thanks for your reply Umesh. But I didn't get you. Where to check the length of column?
Five Stars

Re: [resolved] cannot convert string to date in tMap

check the length of the column in same code.
yourcolumn!=null && yourcolumn!=" " && yourcolumn.length>68? 
TalendDate.parseDate("yyyyMMdd', yourcolumn.replaceAll("-", "")):TalendDate.parseDate("yyyyMMdd', "000000")
One Star

Re: [resolved] cannot convert string to date in tMap

Thank you but if I had different data formats in input column how to parse it.
When input has only two formats "20140224" or "2014-02-24" by using TalendDate.parseDate("yyyyMMdd",row1.Column1), data is parsing to the output.
When date includes  "02/24/2014" with above formats,
I used in tMap as
TalendDate.isDate(row1.Column1,"MM/dd/yyyy")?TalendDate.parseDate("yyyy-MM-dd",Column1):TalendDate.parseDate("yyyyMMdd",row1.Column1))
then could not parse the data.Getting error as
Caused by: java.text.ParseException: Unparseable date: "02/24/14"
What could I do?
Thank you
Five Stars

Re: [resolved] cannot convert string to date in tMap

First get a list of formats then do above logic for replace and then do the parsing that will help. use each parsing according to source format. 
if there are multiple formats then create routine and write all the check and validation to handle date conversion, and then return date object form routine function. this is the best way for you.
One Star

Re: [resolved] cannot convert string to date in tMap

Ya I've checked the source data format. It has the above three formats only. So how could I handle?
What should I modify in the function which I've used
TalendDate.isDate(row1.Column1,"MM/dd/yyyy")?TalendDate.parseDate("yyyy-MM-dd",row1.Column1):TalendDate.parseDate("yyyyMMdd",row1.Column1))
Please provide the solution to me. I'm eagerly waiting for the solution.
Thank you.
Five Stars

Re: [resolved] cannot convert string to date in tMap

try this.
yourcolumn!=null && yourcolumn!=" " && yourcolumn.length>68? yourcolumn.contains("-")?
TalendDate.parseDate("yyyyMMdd', yourcolumn.replaceAll("-", "")):TalendDate.parseDate("MM/dd/yyyy', yourcolumn):TalendDate.parseDate("yyyyMMdd', "000000")
One Star

Re: [resolved] cannot convert string to date in tMap

Hi Umesh,
I have situation that, I have source table with 2 columns as follows..
START_DT           NAME
01/12/2015         PANKAJ
02/27/2010         SURAJ
START_DT has string datatype in source table but i just want it in DATE datatype without changing its values format.
how i can achieve that? give solution..
thanx in advance.

Regards,
Shri_Kul1
Talend User.
Moderator

Re: [resolved] cannot convert string to date in tMap

Hi Shri_Kul1,
You can use built-in function TalendDate.parseDate(string date_pattern, string string_of_date) to convert String to Date in tMap.

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] cannot convert string to date in tMap

Hey Hi,
could you please give me example with that logic you mentioned.
Regards,
Shri_Kul1
One Star

Re: [resolved] cannot convert string to date in tMap

Hey ,
Thanks for your valuable reply , its working .... Thanks a Lot.
Regards,
Shri_Kul1
Talend User.
Moderator

Re: [resolved] cannot convert string to date in tMap

Hi Shri_Kul1,
Please take a look at my screenshot.
Feel free to let us know if it is what you are looking for.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] cannot convert string to date in tMap

Hi,
Exactly, i have done as posted in screen shot.
Thanks Sabrina for your valuable feedback.

Regards,
Shri_Kul1
Talend User.
Moderator

Re: [resolved] cannot convert string to date in tMap

Hi Shri_Kul1,
Great it works. Feel free to let us know if there is any further help we can give.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] cannot convert string to date in tMap

Hi Sabrina,
Please guide on following situation, 
i have columns TRISTARTDATE & TRIENDDATE with unix timestamp values with integer data type & i just want to convert them into a proper date format "MM/DD/YYYY" with date as data type.. i am trying following logic for the same but it giving wrong dates...


Proper dates are mentioned below. 

TRISTARTDATE (source)               TRISTARTDATE(Expected Target)                                      
1425148200                           03/01/2015
1423074600                           02/05/2015
Waiting for your reply
Regrds,
Shri_Kul1
Talend User
Moderator

Re: [resolved] cannot convert string to date in tMap

Hi Shri_Kul1,
Do you want to parse Integer/int to Date? If so, you can first read integer as a String :
(String.valueOf(1425148200))
then analyse the format of the string as a date
TalendDate.parseDate("yyyyMMdd",(String.valueOf(1425148200))).

Let us know if it is Ok with you.

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] cannot convert string to date in tMap

Hi Sabrina,
Ill try this & let you know in 2 hours.
Thanks,
Shri_Kul1
Talend user.
One Star

Re: [resolved] cannot convert string to date in tMap

Hi Sabrina ,
I have applied logic which you described earlier, but seems to be not working properly.
please see the below image for your reference. It giving some error like -  Unparseable date: "1425148200"
          
Error message.  
Moderator

Re: [resolved] cannot convert string to date in tMap

Hi,
Actually, your input is "Long" data type. I see you have *1000 in it. Converting long type to Date type on tMap
 should be new java.util.Date(long number)
Please see my screenshot.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] cannot convert string to date in tMap

Hi Umesh,
Could you help me out for the issues am facing from yesterday.
Please guide on following situation, 
i have columns TRISTARTDATE & TRIENDDATE with unix timestamp values with integer data type & i just want to convert them into a proper date format "MM/DD/YYYY" with date as data type.. i am trying following logic for the same but it giving wrong dates...
Proper dates are mentioned below. 
TRISTARTDATE (source)               TRISTARTDATE(Expected Target)                                      
1425148200                           03/01/2015
1423074600                           02/05/2015


Waiting for your reply
Regrds,
Shri_Kul1
Talend User
One Star

Re: [resolved] cannot convert string to date in tMap

Hi Sabrina / Umesh,
Thanks for your reply , but i am facing same issues , its not giving expected target dates
I have applied the logic you mentioned in your post .

Please see the below image for your reference , you will see the outcome that am getting by this logic.
I don't understand that why these values are converted into only  1970's dates????
 
                  
tMap mapping.
Moderator

Re: [resolved] cannot convert string to date in tMap

Hi,
"dd" is day in month and "DDD" is day in year.
Please check your date pattern.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] cannot convert string to date in tMap

Hey Sabrina,
If you see i have mentioned Date format "DD/MM/YYYY" in tMap @ target side for TRISTARTDATE column.

    
That is my question why that milliseconds are getting converted like that????Smiley Sad


Thanks for your valuable responses...Smiley Happy 
Shri_kul1
Talend User