String to Date and Time is giving issue and not able to resove

[chaithuj]

I am using a simple date format here
My Data
00000000

Want to convert it to Date format
and it works for one column and fails for the other two
the one it worked

The other 2 which fails

please help

[takbb]

Hi @chaithuj , to convert a column to Date, it has to actually contain data which represents real dates. There is no actual date with zeros for day, month and year, ie 0000-00-00 and so the String to Date&Time node cannot convert it.

Since I presume that 00000000 is representative of “missing” data, what you could do is convert this to missing value first. You could then apply String to Date&Time on the column.

One way for converting 00000000 to “missing” is using Rule Engine on each column.

The following rule would achieve this for the Part_Date column, for example:

NOT $Part_Date$ = “00000000” => $Part_Date$

[chaithuj]

Thank you, I will try that
We have Business scenarios where dates will be zeros in certain cases, hence wanted to try this

[nan]

@chaithuj , can you describe your use case? I would like to understand what you are using dates like 0000-00-00 for. If this designates “missing” or “not applicable”, than using the missing value, as takbb described, is the usual way to dot this in KNIME. If you use it for something else, I am very interested.

Have a nice day,
nan

[chaithuj]

Hi,

Its a missing date but the use case is different.
we get participants data from clients sent to us as a data tecord
its a 80 charecter line which holds the plan number, ssn and some imp dates loke hire date, term date, benefits eligibility dates at a certain positions
For example positions 32 to 40 will hold
Eligibility Date. We read the record, fetch the date fields from the record and tey to match them with what is present in iur Database and based on the Business Requirements we either update our Database with right dates sent by Client or update the Client File with what is present in iur Database.
When Dara is missing or rhey dont have data they fill that with zeros and is called zerofill date

So in my Logic I read data file which contains 1000”s of records, parse the date fields and as they are returned in String format, I am trying to convert it to Date. As I have to compare this Date with the Date present in the Database.
This Colunn can get few valid dates and few zerofilled dates

[nan]

Thanks for the explanation. In this case the following might help: Problem retrieving data from MySQL Database using Database Reader node - SOLVED - #4 by ddamasceno

[yogesh_nawale]

Hey @chaithuj,

May be this will help.
If your first row contains 00000000, then the String to Date&Time node cant guess date&time format.

So convert the 00000000 to missing value using Column Expression Node

And then use String to Date&Time node.

See if it works
I also have attached the workflow

Convert Date.knwf (74.9 KB)

Regards,
Yogesh

[chaithuj]

Thank you Yogesh for your time and suggestion. l tried it and worked.
Thank you very much for all the help

[yogesh_nawale]

Thanks for the green tick . Glad to help