Converting String to Date in formula

[smallpotato]

Hi I am new to alteryx. I have a very huge dataset with lots of Date data.

DateA	DateB	DateC	DateD	...
2023-01-31	2023-01-31	2023-01-31	2023-01-31
- -	- -	- -	- -
2023-02-02	2023-02-02	2023-02-02	2023-02-02
2023-02-02	2023-02-02	2023-02-02	2023-02-02
2023-02-02	2023-02-02	2023-02-02	2023-02-02
2023-02-02	2023-02-02	2023-02-02	2023-02-02
2023-02-02	2023-02-02	2023-02-02	2023-02-02
- -	- -	- -	- -
- -	- -	- -	- -

To avoid having conversion error " - - " not a valid date

I read all the input as string first (with input tool), and would like to remove all data with " - - " before I changed back to date as datatype

I get data like "20230131" in string with 8 as size

Since the DateTime tool only works on one column, I would like to use formula tool instead ( to avoid having 2-30 datetime tool in canvas)

I tried formula "DateTimeParse([TMP_RT_DT],'%Y%m%d')" the error of data will be truncated to "2023-01-" due to the size limit of 8.

and by using this, I only get string data that looks like date.

Would like to ask how can i get rid of those " - - " (with no conversion error) and get a date (date as data type) as my output.

[alexnajm]

I would use a Filter tool to remove those rows with the “- -“, then use a Select tool on the columns to change to a Date data type! You could also use the auto field tool in case you are expecting new columns in the future that need to be converted

If you want to go down the Formula route across multiple columns at one time, look at the multi field formula tool - however the first option will likely be easier. 

[flying008]

Hi, @smallpotato 

Change output type to [Date], 

[smallpotato]

I tried something like this, and still got "ConvError: Formula (9): XXX_DT: "2069-09-14" was truncated to 8 characters" as error

[smallpotato]

I tried your method but got 100% null value. My source data is dbf table, so when I read it as string, I got data like 20230103, it could not be changed to date directly using select tool

[smallpotato]

and i still wanna avoid adding additional column, since there is really lots of data

[flying008 · Accepted solution]

Hi, @smallpotato 

As above post, maybe you can use Multi-Field Formula tool to get your want. because it can change your output type of field.

[alexnajm]

Ah this was not specified before - the multi field formula option works best then

[smallpotato]

thanks seems work for me

[flying008]

Hi,@smallpotato 

If can help you get your want, please mark it as a solution and give a like for more share.