Invalid Date - type is datetime

Question

So I am trying to reformat a date value to

if !isnull([Customer Date Of Birth]) then DateTimeFormat([Customer Date Of Birth],'%d/%m/%Y')
else null()
endif

some of the errors

ConvError: Formula (88): Date_Of_Birth: 25/12/1974 is not a valid Date
ConvError: Formula (88): Date_Of_Birth: 01/01/1938 is not a valid Date
ConvError: Formula (88): Date_Of_Birth: 22/01/1979 is not a valid Date
ConvError: Formula (88): Date_Of_Birth: 18/10/1959 is not a valid Date
ConvError: Formula (88): Date_Of_Birth: 01/01/1969 is not a valid Date
ConvError: Formula (88): Date_Of_Birth: 10/12/1985 is not a valid Date

Customer Date Of Birth is of datetime type  in this format

1950-01-01 00:00:00

2000-01-01 00:00:00

2016-01-01 00:00:00

2017-01-01 00:00:00

1970-01-01 00:00:00

tmb088 · Answer

Date Time arse didnt work

ConvError: Formula (88): DateTimeParse: Cannot convert "1973-05-24 00:00:00" to a date/time with format "%d/%m/%Y" and language "English": Expected separator '/%m/%Y', got: '73-05-24 00:00:00'

DataNath · Answer

Hey @tmb088, there's 2 choices here. At the moment, if you're doing this in the normal Formula tool, you'll need to create a new field that is a string. Alteryx can only handle DateTime datatypes in ISO format i.e. YYYY-MM-DD HH:MM:SS and within the standard Formula tool you can't change the data type when overwriting a field, therefore, when you change it to a custom format of DD/MM/YYYY (where it will become a string), you can't then place this within the same field where it was previously a DateTime.

However, the Multi-Field Formula allows us to change the output data type of a field when overwriting it, so that's an option if you don't want to create a new field > delete the old > rename the new one. Here's a workflow with the 2 approaches - hope this helps!

Reformat customer DoB.yxmd

MarqueeCrew · Answer

@tmb088

Please try using DateTimeParse()

cheers,

mark