cancel
Showing results for 
Search instead for 
Did you mean: 

DateTime Tool Enhancements: Convert Multiple FIelds & Overwrite Existing Field

The DateTime tool is a great way to convert various string arrangements into a Date/Time field type. However, this tool has two simple, but annoying, shortcomings :

 

  1. Convert Multiple Fields: Each DateTime tool only lets you convert one field. Many Alteryx tools (MultiField, Auto Field, etc.) allow you to choose what field(s) are affected by the tool.  If I have a database with a large number of string fields all with the same format (such as MM/DD/YYYY), I should be able to use one DateTime tool to convert them all!
  2. Overwrite Existing Field: The DateTime tool always creates a new field that contains your converted date/time. I ALWAYS have to delete the original string field that was converted and rename the newly created date/time field to match the original string field's name. A simple checkbox (like the "output imputed values as a separate field" checkbox in the Imputation tool) could give the flexibility of choosing to  have a separate field (like how it is now) or overwrite the string field with the converted date/time field (keeping the name the same).

Alteryx is overall an amazing data blending software. I recognize that both of these shortcomings can be worked around with combinations of other Alteryx tools (or LOTS of DateTime tools), but the simplicity of these missing features demonstrates to me that this data blending tool is not sufficiently developed. These enhancements can greatly improve the efficiency of date handling in Alteryx.

 

STAR this post if you dislike the inflexibility of the DateTime tool! Thank you!

7 Comments
pbrink
Meteor

Agree I need to know how to combine all the multiple dattime fields back to the 1 end table.

RodL
Alteryx Alumni (Retired)

Not to say that your suggestion isn't warranted, but just want to make sure you are aware that you can do all of what you described using the single Multi-Field Formula tool.

It will take multiple String fields with a specific date format and, using the DateTimeParse() function, convert all of the fields at once (and replace the existing fields if desired).

Granted you need to create the expression with the necessary Specifiers (check out Help for the entire list), instead of having the UI in the DateTimeParse tool, but it is still relatively easy to do what you are asking.

pbrink
Meteor

I see that now. Thank you. I am using the formula but I am having a problem with really bad datetime field data and receive this error using this formula.  This formula DateTimeFormat([RB_LDPHOMERCV_TMST],"%Y-%m-%d")

2017-02-10_12-03-17.png

RodL
Alteryx Alumni (Retired)

@pbrink,

You might want to take this question/discussion to one of the discussion pages and post it there. It's easier to interact there (and more people will see it, rather than here on the Ideas pages).

Since it looks like the conversion errors are directly in the Input Data tool, it appears that Alteryx is being told by the source that it is a DateTime data type (which it isn't). When you post the question on a discussion page, if you can provide a sample of the data, that will help with troubleshooting.

DultonM
Bolide

@RodL - Your Multifield Formula Tool work-around is great! Thank you!

DultonM
Bolide

Wanted to share with everyone following this idea that @BarnesK built a macro that perfectly addresses the 2 enhancements. It completely replicates the DateTime tool (without using the DateTime tool!) plus has these new features!

Quasar

 this would be a great addition to the core toolset.

 

cc: @AdamR (possibly something we could iterate as a community?)