Irregular date format

[lyd_ucl]

I have an exported dataset that records when people are completing a survey. Unfortunately, the way the survey software records the date/time means that they are varying lengths, for example, both of the below are in the dataset:

2019-02-20 21:45:19 

2019-02-21 1:53:09

Does anyone know an easy way to add a 0 in front of the 1, but only if it's the abbreviated number?

I've considered doing an IF using Left/Right to identify that single character, but wondered if there's a cleaner way to do this?

Thanks!

[MarqueeCrew · Accepted solution]

@lyd_ucl ,

When Alteryx encounters your data, it will treat it as a STRING data type.  To convert it to either a DATE or DATE/TIME data type, you'll use a FORMULA tool.  You'll create a new variable and use the following formula:

DateTimeParse([Survey Date],"%Y-%m-%d %H:%M:%S")

This will work to  create the format that you are looking for.  If you really don't want a DATE/TIME and want it as a string, that works too and you don't need to create the new variable.  In fact, you can write to the same string variable and later change it to a DATE or DATE/TIME via a SELECT.

Cheers,

Mark

[danilang]

hi @lyd_ucl 

As long as that's the only irregularity in the date then the following will work.  Basically, split the string on the space and pad the time component out to 8 characters

Dan

[ponraj]

Attaching a sample workflow.  You can use DateTime tool to standardize your date and times. 

[lyd_ucl]

Thanks all for your help - I was worried the inconsistancies would mean the conversion would be tricky, but it actually worked well.

Thanks again!