Date time conversion error

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.

Errors.PNG

Date Time

Accepted answers

jrgo

@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.

All comments

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

@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?

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335