topic Re: Date conversion in Alteryx Designer Desktop Discussions

Date conversion

timsmith — Wed, 01 Mar 2017 21:54:05 GMT

Hi, I've been looking through the forums but haven't quite figured out my date conversion problem.

I've gotten the date into this format:

3 Feb. 2008

and have tried this formula to get it into a date format:

DateTimeFormat(DateTimeParse([Date2],'%d/%b/%Y'),'%m-%d-%Y')

but I get null values where the day is a single digit, e.g.: 3 Feb. 2008

Specifically, I get a message that the day of month value is out of range 1..31. Somehow, when the day is a single digit, the month value isn't seen properly.

Thank you for any help.

Re: Date conversion

MarqueeCrew — Wed, 01 Mar 2017 22:24:47 GMT

@timsmith,

If your target variable is a DATE field (a valid date), then this formula is what you're looking for:

DateTimeParse([Date2],'%d %b. %Y')

As you can see, it takes your input 3 Feb. 2008 and reformats it to: 2008-02-03

It expects to see a DAY# followed by a SPACE followed by a MONTH ABBREVIATION followed by a PERIOD followed by a SPACE followed by a YEAR.

Hopefully this solves your challenge.

Cheers,

Mark

Re: Date conversion

timsmith — Wed, 01 Mar 2017 22:41:59 GMT

Mark,

Thanks very much for your reply.

I tried that but am still getting errors for the single-digit days. Here's what I see for Jan. 1, 2017, for example:

I has no trouble with any days of the month greater than 9; it's only the single digit days that I see errors for.

-Tim

Re: Date conversion

MarqueeCrew — Wed, 01 Mar 2017 22:48:25 GMT

3 Feb. 2008 and

Jan. 1, 2017

require different formulas.

The formula that I provided solves for the first case. While I can solve for the second case, is your input a mixture of different formats? If it is, then you'll need to check the format for any given field before converting it.

DateTimeParse([Date2],'%b. %d, %Y')

Does this make sense?

Mark

Re: Date conversion

timsmith — Wed, 01 Mar 2017 23:01:18 GMT

I understand -- and I wasn't clear about the data I am working with.

All the data look like this, which shows what you see in the Date2 field on the far right:

The formula works fine for all but the first two rows, the ones with the single digit day numbers, Feb. 4 and Jan. 7. I've tried taking out leading and trailing whitespace but that didn't solve the problem.

Thanks again Mark for help me with this.

Tim

Re: Date conversion

MarqueeCrew — Wed, 01 Mar 2017 23:03:19 GMT

can you save the date2 data to an EXCEL :( file and post it. I'll reply with a solution promptly.

Thanks,

Mark

Re: Date conversion

timsmith — Wed, 01 Mar 2017 23:09:39 GMT

Sure. Here you go.

Thanks very much, Mark!

Re: Date conversion

MarqueeCrew — Wed, 01 Mar 2017 23:16:16 GMT

DateTimeParse([Date2],'%d %b. %Y')

That works 100% of the time with the file that you gave me.

If you have whitespace, try:

DateTimeParse(Trim([Date2]),'%d %b. %Y')

I can watch you via WebEx now and control your screen to get you a 100% solution working. For me, it looks fine with your data. If you want a silent webex, please PM me with your email & I'll set it up.

Cheers,

Mark

Re: Date conversion

MarqueeCrew — Thu, 02 Mar 2017 00:40:54 GMT

@timsmith,

Magic! Not the good kind. I can't explain why it is the way that it is, but if the input to the formula is saved and then read into the formula, it works. My solution required me to REFORMAT the date using a regular expression and then padleft the date if it was a single digit. Then the parse function worked perfectly.

@BenG, if you have time to work with me and Tim, please contact me (not today) and I'd like to explore this with you. Literally, the adobe inputs were replaced with text inputs (sourced by browsing their output, cutting and pasting them to another workflow) and my original solution worked.

This was all conducted with version 10.6

Cheers,

Mark

Re: Date conversion

timsmith — Thu, 02 Mar 2017 00:43:05 GMT

Can't thank you enough, Mark! I appreciate your persistence in figuring this out.

Tim

Re: Date conversion

timsmith — Thu, 02 Mar 2017 16:53:10 GMT

When I added a full year's worth of data, I see that because "May" is not abbreviated with a period at the end, I get null values for all dates in May. Is there any way to adjust the Regex so that it works for May dates?

Tim

Re: Date conversion

MarqueeCrew — Thu, 02 Mar 2017 17:37:34 GMT

put the following in front of your formula:

REPLACE(

Then put this at the end:

,"May.",'May')

That will cause only May to lose the .

Re: Date conversion

timsmith — Thu, 02 Mar 2017 23:09:27 GMT

That works -- thanks very much!

-Tim