Date Conversions

[rdp]

Hi Rod,

I'm really struggling with getting my String to convert to date format when looking at http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Date-Conversions/ta-p/3587  When I originally connected to the data my Start Date was a string which I then parsed out to be in their own columns .  

Then, I concatenated Month Date Year field and that looks great but I need to now convert that from a String to a Date.  I've tried using the DateTime in Parse and that did not work.  I also tried just changin the format in my Outfield Type to Date.  Either way I get NULL in the field.  This is my first time using Alteryx and I'm hoping that's why I am struggling so much with this.

I appreciate any assistance you can offer.

Renee

[AndyM]

Renee,  I suspect your problem with the DateTimeParse function is that you are not including a leading zero for the Date column.   It does require a leading zero to be able to parse correctly.  If you add leading zeroes where required so that it is always two digits, the DateTimeParse function should work.  

Alternatively, you could format your date field to the following format "2015-11-05" or "YYYY-MM-DD" as a string data type.  If your date is in this format and is a String field, you can simply change the data type from String to Date.  Again,  you will need leading zeroes on the day and month fields so that there is always two digits.

Andy 

[rdp]

Andy,

I added a leading zero here and then changed to a Date format.  Returned all NULL values.  I'm thinking that maybe this is not where I need to add the leading zero?

[MarqueeCrew]

you might try 

RIGHT(PADLEFT([Month],2,"0"),2)+'-'+RIGHT(PADLEFT([Date],2,"0"),2)+'-'+[Year]

[RodL · Accepted solution]

Renee,

Looks like the logic is a bit off. 

First of all, you wouldn't need to add a leading zero to the month (since it is actually "Nov" anyway).

Also, "hard-coding" a leading zero can be problematic if the date already has two characters. So the best way is to make use of the PadLeft function which will supply a leading zero if needed. 

And finally, you need to be careful when mixing strings and numbers.

I've attached a simple example that covers all of these and I think gets you the output you want (as a date type).

Thanks for the question!

Rod

[rdp]

Thank you so much.  This really helped.

[rdp]

Thank you for your help.  I will have to get familiar with these expressions.  I would never have gotten to this on my own. 

[rdp]

Rod,  I've parsed my times too ( hour field and minutes field), now I want to bring them together into one field so I can then do a calcultion of between start data and end date or cancelation date. 

How do I reformat the parsed fields back into one field?

Also, is there a place to learn more about this?

Renee

[MarqueeCrew]

would you be available now if I setup a WebEx?

[rdp]

MarqueeCrew -

My original date field with time exported as test from the system so I had to parse the fields out Day Month Year Hour Minutes.  I've been able to concatenate the start month day year as a Date field but now I need to do the same for just the time so I can get hours and minutes in one field.  I tried this formula:  DateTimeParse([Start Hour] + [Start Minutes],'HH:mm') but it brought back the right format but none of my rows contained any hours or minutes just 00:00:00