String to Date Conversion

[Samir_Nagure]

Hi, I want to convert the column from string to date format. Date is given in excel file as follows
25-04-23 16.42 (25-04-2023 4.42.28 PM)

I tried to extract only date by using string manipulation, however I am not getting proper column in date format.
In the String to Date&Time note the date is not accepting and showing error as “No suitable format found!”. Not sure how to do it. Could I get a help here please.

Regards,
Samir

[takbb]

Hi @Samir_Nagure ,

You need to overtype the date format with a date/time format mask which matches the date and time in the data. Based on your sample data, this should do it:

d-M-yy H.mm

[Samir_Nagure]

@takbb

d-M-yy H.mm

Hi,

I tried to make changes as you mentioned, however its still not working.

[takbb]

Hi @Samir_Nagure , I know this will sound silly, but what happens if you just ignore that message and run it anyway

[Samir_Nagure]

Hey, its showing the missing values (?) in the column.

[Samir_Nagure]

Sorry, its giving me below error message

“Execute failed: Failed to parse date in row ‘Row1_Row190’: Text ‘25/04/23 12:48:37’ could not be parsed at index 2”

[takbb]

Comparing your two config screenshots, you appear to have some data which is 25/04/23 format, and others which is 25-04-23 16:42 format

And I just realised that in your first config screenshot, the date format of the “first cell” in your screenshot doesn’t match the format you described ( the date separator is / instead of - )

Also in the first you are trying to return just date, whereas in the second your have it trying to return Date&Time.

So what is the actual format of your data, and are you trying to return as Date, or Date&Time?

Maybe upload a sample ?

[Samir_Nagure]

Okay, let me explain it to you.

1. In the excel file the date format is given as attached screenshot (refer “Date in Excel” screenshot)
   

2. When I import the raw data into knime, the date is showing as 2nd screenshot (below screenshot)

3. Now I am using the String Manipulation node to extract the date from the string, which is my new column as 3rd screenshot (below screenshot)

Now, what I want is, either convert the string to date from 2nd option (from imported data in knime) or 3rd option (New Date Extracted using String Manipulation).

Hope this helps to understand the my query.

Thanks in advance!

[takbb]

ok, I understand now.

I thought from your question you were trying to turn the datetime string 25-04-23 16.42 into a date

You would be able to that directly using the original config I posted, with the format d-M-yy H.mm which will take a date/time in the format d-M-yy H.mm and return a Date column.

But, if having imported into KNIME the date is actually in the format 25/04/23 12:48:37 (gaining some seconds from somewhere )

You would be able to extract the Date from that by specifying a format mask of

d/M/yy H:mm:ss

If you are trying to apply it to a date/time string that you have extracted using String Manipulation which is in the format 25/04/23

You would be able to extract the Date from that (turning it into a DATE column) using the format mask

d/M/yy

in all cases, you want to specify that the NEW data type is “Date”

(There is no need to use the String Manipulation to first extract the date part of the string)

Finally, if it could be in ANY of the aforementioned formats, you could extract it using a format mask of

d[/][-]M[/][-][yyyy][yy][[ ][H[.][:]m[[.][:]s]]

or you could try out a new component I’ve been working on

Parse Date&Time – takbb

[Samir_Nagure]

@takbb Thanks a lot for the detailed solution. I will try all of them and let you know if I face any issues. I will also try to use your component in my flow to see if it fits in.

Thanks a lot again!

Regards,
Samir