topic Re: extracting date and time as separate columns from same column in Talend Studio

extracting date and time as separate columns from same column

Anonymous — Sat, 16 Nov 2024 03:47:37 GMT

Hi all,

I have a requirement to extract date and time as separate column from the same column.

my input column has data like "2018-09-27 18:48:24"

i want column1 to have data like "2018-09-27" and column2 to "18:48:24".

i tried TalendDate.formatDate("yyyy-MM-dd", input column name), but it didn't worked.

Re: extracting date and time as separate columns from same column

manodwhb — Wed, 18 Dec 2019 07:10:12 GMT

@keshav1991 , you need to convert date data type to sting and then split based on space.

TalendDate.formatDate("yyyy-MM-dd hh:mm:ss", input column name)

Re: extracting date and time as separate columns from same column

Anonymous — Wed, 18 Dec 2019 07:20:16 GMT

Hi @manodwhb ,

i need to convert from the input column right?

Regards,

Keshav

Re: extracting date and time as separate columns from same column

manodwhb — Wed, 18 Dec 2019 07:21:50 GMT

@keshav1991 , yes

Re: extracting date and time as separate columns from same column

Anonymous — Wed, 18 Dec 2019 07:27:16 GMT

I Have changed the input column to string and i used the "formatDate" function, but no luck.

screenshot_4.jpg

Re: extracting date and time as separate columns from same column

Anonymous — Wed, 18 Dec 2019 07:27:54 GMT

attaching screenshot from tmap

screenshot_5.jpg

Re: extracting date and time as separate columns from same column

manodwhb — Wed, 18 Dec 2019 07:47:02 GMT

@keshav1991 , what is the source data type for relationship_created_on ,if it is string, then you can split based on space.

Re: extracting date and time as separate columns from same column

knknaveen — Wed, 18 Dec 2019 08:51:30 GMT

Hi Keshav,

formatDate output will be of string type. So, you need to parse it to date type before loading it into a date column.

Below script will help you split date & Time stamp separately.

TalendDate.parseDate("yyyy-MM-dd",TalendDate.formatDate("yyyy-MM-dd",row2.HIRE_DATE_TIMESTAMP ))

TalendDate.parseDate("HH:mm:ss",TalendDate.formatDate("HH:mm:ss", row2.HIRE_DATE_TIMESTAMP))

PFB TMap configuration for reference.

Re: extracting date and time as separate columns from same column

Anonymous — Wed, 18 Dec 2019 10:03:17 GMT

Relationship_created_on column is of datetime datatype, and when i test it from tmap console i am getting below error.

screenshot_6.JPG

Re: extracting date and time as separate columns from same column

Anonymous — Wed, 18 Dec 2019 10:04:10 GMT

attaching the error screenshot.

do i need worry about it or simply i can ignore and move forward

screenshot_7.JPG

Re: extracting date and time as separate columns from same column

Anonymous — Thu, 19 Dec 2019 06:58:35 GMT

Hi @knknaveen ,

i have made changes as per you screenshot and script,but syill i'm getting below error.

[statistics] connecting to socket on port 4045
[statistics] connected
Exception in component tMap_1 (STG_TO_WAREHOUSE_REPO)
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "2019-06-01"
at routines.TalendDate.parseDate(TalendDate.java:942)
at routines.TalendDate.parseDate(TalendDate.java:886)
at local_project.stg_to_warehouse_repo_0_1.STG_TO_WAREHOUSE_REPO.tDBInput_2_inProcess(STG_TO_WAREHOUSE_REPO.java:2468)
at local_project.stg_to_warehouse_repo_0_1.STG_TO_WAREHOUSE_REPO.runJobInTOS(STG_TO_WAREHOUSE_REPO.java:5829)
at local_project.stg_to_warehouse_repo_0_1.STG_TO_WAREHOUSE_REPO.main(STG_TO_WAREHOUSE_REPO.java:5678)
Caused by: java.text.ParseException: Unparseable date: "2019-06-01"
at java.text.DateFormat.parse(DateFormat.java:366)
at routines.TalendDate.parseDate(TalendDate.java:928)
... 4 more
[statistics] disconnected

screenshot_8.JPG

Re: extracting date and time as separate columns from same column

Anonymous — Thu, 19 Dec 2019 08:47:18 GMT

For the error 'java.lang.RuntimeException: java.text.ParseException: Unparseable date: "2019-06-01"'
You should use the pattern as "yyyy-MM-dd" in the function TalendDate.parseDate("yyyy-MM-dd",xxxx)

BTW, please insert the images into your post body instead of attaching it.

Re: extracting date and time as separate columns from same column

Anonymous — Thu, 19 Dec 2019 08:59:33 GMT

Thank you @shong , date issue looks resolved, but time is still giving error as it has issues.

Re: extracting date and time as separate columns from same column

Anonymous — Mon, 23 Dec 2019 04:23:03 GMT

What is target app? I see the data type of these two columns are defined as Date, the time string are converted back to a Date.

Re: extracting date and time as separate columns from same column

Anonymous — Mon, 06 Jan 2020 08:41:56 GMT

Hi @shong ,

I found that the source column had data only for HH:mm so i changed the from HH:mm:ss to HH:mm. it worked.