One Star

[resolved] extract year from date type variable

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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: [resolved] extract year from date type variable

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
11 REPLIES
Community Manager

Re: [resolved] extract year from date type variable

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] extract year from date type variable

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
One Star

Re: [resolved] extract year from date type variable

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
Community Manager

Re: [resolved] extract year from date type variable

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] extract year from date type variable

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
Community Manager

Re: [resolved] extract year from date type variable

Try this expression:
Long.parseLong(""+row1.c1+String.format("%03d",row1.c2)+String.format("%03d",row1.c3)
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] extract year from date type variable

That's perfecto shong...
One Star

Re: [resolved] extract year from date type variable

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

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

Re: [resolved] extract year from date type variable

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
One Star

Re: [resolved] extract year from date type variable

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