Date time conversion error

[logzhang]

My input is a csv file so the data type are all strings. I converted the date fields to date time type using SELECT tool. However, I am getting the conversion errors. I know it may not impact the final result but is there an easier way to get rid of them? See below the errors.

[estherb47]

Hi @logzhang 

The Select tool will only convert string dates into real dates when they're already in the Alteryx format (e.g., 2019-12-23 16:09)

For other formats, you'll need to use the Date Time tool or a Formula tool for each field.

For multiple fields, if they have the same schema, you can use a Multi-Field Formula tool and use [_CurrentField_] inside the DateTimeParse formula.

Let me know if that helps.

Cheers!

Esther

[fmvizcaino]

Hi @logzhang ,

To successfully convert a string to a date format in a select tool, you need to have the exact format of an Alteryx date, which is yyyy-mm-dd hh:mm:ss (ex: 2019-01-01 11:55:13)

To convert your date into a readable Alteryx date format, you can use the datetimeparse function.

[bpatel]

hi @logzhang ,

Alteryx identifies this as a proper datetime format 

yyyy-mm-dd HH:MM:SS

you might need to convert the date first. i hope this helps!

[jrgo · Accepted solution]

@logzhang 

The date is not in the correct ISO format. Noticing that you're attempting to convert various fields to datetime, you can use the expression below in a Multi-Field Formula tool:

IF ISEMPTY([_CurrentField_])
THEN NULL()
ELSE DATETIMEPARSE([_CurrentField_], '%m/%d/%Y %H:%M:%S')
ENDIF

This will first check to see if the value is empty. If it is, it'll convert to NULL, which is a valid value for DATETIME. If it's not empty, it'll attempt to parse. 

[logzhang]

Take the answer from @jrgo. Thank you all!

[logzhang]

@jrgo I do not need the hour minute and seconds. Is there a way to get rid of them?

[fmvizcaino]

Hi @logzhang ,

If you change your field type to Date, you will automatically get rid of hour information.

Best,

Fernando Vizcaino

[logzhang]

Thank you.