In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Invalid Date - type is datetime

tmb088
6 - Meteoroid

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

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus

@tmb088 

 

Please try using DateTimeParse()

 

 cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
DataNath
17 - Castor
17 - Castor

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!

 

100001.png

tmb088
6 - Meteoroid

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'

 

Labels
Top Solution Authors