community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

datetime error with formula DateTimeFormat([RB_LDPHOMERCV_TMST],"%Y-%m-%d")

Asteroid

I am using the Formula app and using this formula DateTimeFormat([RB_LDPHOMERCV_TMST],"%Y-%m-%d")

this converts most of my datetime fields to dates but it does not know what to do with fields that are entered incorrectly 0005-12-23 or 0026-01-12.

 

I receive an is not a valid datetime error2017-02-10_12-41-45.png2017-02-10_12-03-17.png

Alteryx Alumni (Retired)

Is there any consistency as to what the first 4 characters in your invalid dates actually mean?

Also, I assume that the fields you are converting are String data type and you are trying to get to a true DateTime format? If so, you would want to be using the DateTimeParse function instead of the DateTimeFormat.

Asteroid

No it is just bad input data.  The original field is a datetime field . I really just want to exclude the bad fields.

2017-02-10_14-51-11.png

Alteryx Alumni (Retired)

So then you are really just trying to make those invalid dates to be NULL values?

If that's the case, you should just be able to use a Select tool out of the Input and set the datatypes to DateTime (it may automatically do that since the database is pushing it out as that).

You will still get the conversion errors, but those errors won't stop a workflow from running...just replace whatever it can't convert to a NULL...which would be what you want...I think.

Asteroid

So I have the select tool it says datetime and still produces the error I see no where to change those to nulls2017-02-10_15-08-27.png

Highlighted
Alteryx Alumni (Retired)

If you look at the actual data coming out of the Select tool, are the values for those records not NULL?

Labels