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

[pbrink]

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 error

[RodL]

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.

[pbrink]

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

[RodL]

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.

[pbrink]

So I have the select tool it says datetime and still produces the error I see no where to change those to nulls

[RodL · Accepted solution]

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