topic Re: [resolved] extract year from date type variable in Talend Studio

[resolved] extract year from date type variable

Anonymous — Wed, 04 Feb 2015 12:48:08 GMT

Hello All,
I need to convert Start_Date Column which is date datatype ex: "2012-12-26" to Start_Date_Key as 20121226 INT Datatype.
I can easily do it in SSIS with the expression (YEAR((DT_DBDATE)START_DATE) * 10000) + (MONTH((DT_DBDATE)START_DATE) * 100) +DAY((DT_DBDATE)START_DATE).
How to achieve the above solution in TOS. I tried and no result came.
Please help.
Thanks ---> D

Re: [resolved] extract year from date type variable

Anonymous — Thu, 05 Feb 2015 04:19:31 GMT

Hi
In Talend, you can convert the data with date type to an int data with this expression:
source input--main(row1)--tMap--tLogRow

TalendDate.getPartOfDate("YEAR",row1.YouDateColumnName)*10000+(TalendDate.getPartOfDate("MONTH",row1.YouDateColumnName)+1)*100+TalendDate.getPartOfDate("DAY_OF_MONTH",row1.YouDateColumnName)

Shong

Re: [resolved] extract year from date type variable

Anonymous — Thu, 05 Feb 2015 12:03:50 GMT

Hello Shong,
I really appreciate your answer.
i worked with the same code yesterday and executed the "Test" button in tmap Component to check for the result, but it is showing error!!cannot be resolved. Given default value to my date column.
When i run my job, expected values are dumped to my DB Table. thats cooool.
i request you to suggest on how to post a new topic in talend forum instead of reply to post which exists already.
Thanks---->D

Re: [resolved] extract year from date type variable

Anonymous — Thu, 05 Feb 2015 12:58:22 GMT

Hello Shong,
I have a similar doubt. i can do easily in SSIS. Confused to do the same thing in TOS as it involved JAVA.

i need to replicate 3 zero's (000) and add 2nd column which will be 004, same for 3rd column..003
finally it should look like 1st + 2nd +3rd column : 20140203004003
waiting for your early reply
Thanks---->D

Re: [resolved] extract year from date type variable

Anonymous — Thu, 05 Feb 2015 13:04:50 GMT

Hi
I don't understand your last requirement, do you just want to append "003" to the int data? For example:
"yourLastIntData"+"003"
Can you please explain it with more details?
BR
Shong

Re: [resolved] extract year from date type variable

Anonymous — Fri, 06 Feb 2015 05:39:55 GMT

Hello,
Actually not able to add screenshot.
here are my three columns and their values.
Start_Date(Long) | Unit(integer) | Source(integer)
20140203| 4| 3
Outcome should be concatenating all 3 columns and for 2nd and 3rd we need to supply it as 3 digits like "004" and "003"
Finally it should be "KEY"(long)=20140203004003
Thanks--->D

Re: [resolved] extract year from date type variable

Anonymous — Sun, 08 Feb 2015 09:05:46 GMT

Try this expression:

Long.parseLong(""+row1.c1+String.format("%03d",row1.c2)+String.format("%03d",row1.c3)

Re: [resolved] extract year from date type variable

Anonymous — Mon, 09 Feb 2015 12:40:46 GMT

That's perfecto shong...

Re: [resolved] extract year from date type variable

Anonymous — Mon, 09 Feb 2015 12:57:32 GMT

Hi Shong.
I have a string like "03-12-2012" in "MM-dd-yyyy" format.
i need to get result as "20120312" which is ""yyyyMMdd" format.
i tried with right and left string functions to get "yyyyMM" and stuck in getting "dd".
Do i need to create routine for that? or is there any built in function equivalent to SubString() in TOS?
Thanks--->D

Re: [resolved] extract year from date type variable

Steven_Crawford — Mon, 09 Feb 2015 13:09:59 GMT

new SimpleDateFormat("yyyyMMdd").format(newSimpleDateFormat("MM-dd-yyyy").parse(row1.date))

Re: [resolved] extract year from date type variable

Anonymous — Tue, 10 Feb 2015 04:57:32 GMT

Hi Steve,
Can you please be more specific? i am new comer to TOS. I don't know where i have to place the above expression whether in tmap or somewhere else?
Thanks--->D

Re: [resolved] extract year from date type variable

Anonymous — Tue, 10 Feb 2015 05:42:02 GMT

Hi Shong,
I have date column date datatype "03-02-2014" after doing transformation with the expression TalendDate.getPartOfDate("YEAR", row1.date )*10000 + TalendDate.getPartOfDate("MONTH", row1.date )*100+TalendDate.getPartOfDate("DAY_OF_WEEK", row1.date )
i am getting wrong output 20140201 which is not expected.
expected one is 20140302.
can you please help?
Thanks---->D