Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

ToDate(x) vs DateTime tool

cristian_m
5 - Atom

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!

 

 

4 REPLIES 4
JosephSerpis
17 - Castor
17 - Castor

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.

 

Dates_31102020.JPG

cristian_m
5 - Atom

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
17 - Castor
17 - Castor

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.

 

Multi_Field_31102020.JPG

SandeepBabuS
8 - Asteroid

Hi Joseph,

 

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

 

Thanks a lot.

Labels