ToDate(x) vs DateTime tool

[cristian_m]

Hi everyone,

Looking for some clarification on the role of the ToDate(x) function. I checked a few discussions on the forum, but couldn't find any clear answer. 

As per documentation:

ToDate

ToDate(x): Converts a string, number, or date-time to a date.

As I understand it, this refers to the data type of an incoming field being converted into a Date type. However this doesn't seem to be the case. 

Example: ToDate(DateTimeParse([My_field],"%d-%b-%y")) .

Here, a String field of the "%d-%b-%y" format is passed to the DateTimeParse function, which converts it to standard ISO, while still maintaining the String field type. Next, ToDate() should convert it to a Date type as per documentation, but when checking the output anchor's metadata, this doesn't seem to be the case (still a string).

If using the DateTime tool instead, with the String to Date/Time option and the correct input format, both the field format and data type chage.

Help appreciated, thanks!

[JosephSerpis]

Hi @cristian_m are you changing the data type in the formula tool to a Date as I tested your syntax and the field created was a valid date field. However you can simplify your expression to DateTimeParse([My_field],"%d-%b-%y") and it would convert your string field into a date format.

[cristian_m]

Hi Joseph, thank you for your answer; it didn't allow me to do it as I was overwriting an existing field, the drop-down is only active when saving to a new field. As you mentioned, when saving to a new field, DateTimeParse() and Date type from the drop-down are enough for conversion.

I believe this might be a bug as the initial workflow executed without warnings or errors; i.e. when an (outer) data type conversion function does not match the existing field's data type in case of overwrite, it should be flagged somehow? 

[JosephSerpis]

Hi @cristian_m I replicated the scenario you described with updating an existing field and find it odd too that it does not even produce a conversion error in the workflow results. Might be worth suggesting this an idea . If you are updating and existing field and do not want to create a new field may I suggest the Multi-Field tool as it has the ability to change the data types of the existing field. Also DateTimeParse([My_field],"%d-%b-%y") would be sufficient to converting the string into a date format using the Multi-Field Tool.

[SandeepBabuS]

Hi Joseph,

Your solution i.e., DateTimeParse() has helped me to solve one of my challenges.

Thanks a lot.