DateTime format and lay out

[petite]

Hi,

currently my date looks like this: 2016-01-05.

I need to lay it out as 05/01/2016 (UK style) and have it in Date or Date/Time format.

Using Formula tool I get desired lay out but it's a string.

DateTime tool gives me Date/Time format but gives only 2016-01-05 lay out.

Any suggestions?

[DultonM]

Hi @petite! My understanding is that YYYY-MM-DD is the ONLY Date format for Alteryx. Alteryx expects you to convert whatever fields you wish to be a Date format to that structure (via the DateTime tool for example). Otherwise, you won't be able to perform calculations using Date functions. My recommendation is to use the Date field type (YYYY-MM-DD format) throughout your workflow then at the very end convert the field to a string in your desired UK format with the Formula tool. Probably not the answer you wanted, but I find that once you get used to the Alteryx date format, you'll enjoy the consistency of a single date structure for calculations.

[mbarone]

Alteryx uses the ISO data format of YYYY-MM-DD.

That's the only recognizable "Date" format.  Everything else will be a string.

I would follow the suggestion above, where you build your module using YYYY-MM-DD, and at the very end, right before your Output tool, then switch it over.

[PaulT]

The Date Field Type in Alteryx requires that the format be YYYY-MM-DD. The advantage of this is that you can now do math with the date functions. However, we do also realize that for reporting purposes in the final output of your workflow you may want another date format. This is where the Date/Time tool comes in to play. Many people use this tool to convert their dates from string format (ie. Anything other than YYYY-MM-DD) to the YYYY-MM-DD format. However, a commonly missed function of this tool is that it works the other way as well.

As you can see in this screenshot, you will want to take a Date Field and convert it to a formatted string. In the box below you are declaring what format you want your output to look like (in your case DD/MM/YYYY). The tool outputs a new field called DateTime_Out which will contain your newly formatted string.

This avoids you having to use a Formula tool and breaking apart the date string and pieceing it back together manually.

Hope that helps!

[RJS]

Finally the right tool! It was there the whole time! 

I do have a question if you might be able to assist.

I converted my date to a string "Mon dd" and when I try to sort ascending it groups it by month. Nov 1-30 then it starts Dec at Dec 1-31. How can I get it to go "Nov 1 - Dec 31"  Thank you

[PaulT]

Hi RJS,

I'm curious about exactly how your date looks. You have a string field that just says:

Date

Nov 29

Nov 30

Dec 01

And from here you're trying to sort?

I would expect it to sort using alphanumeric logic rather than date logic. If I had a little more detail I might be able to help.

Cheers,

Paul

[RJS]

Hi Paul
Yes I have a date field converted to a string "Mon dd".

My query pulls in the last 30 days from 2 sources then its sorted, summed by day, and then and then joined the converted date columns.

If you can see below the sort comes out alphabetical putting DEC firs then NOV last the the dd numbers in proper sort order.
I've tried to reconvert it to a date but to no avail...
Any help sorting from Nov thru Dec?
Thank you for responding


[cid:image001.jpg@01D37B79.F41CBE20]


Rich Simmons

[RJS]

Well OK

After some egg nog and a few days away I realized I can sort on the actual system date format to make the text date roll in sequential order. I don't need to include the system date in my output either.  Thanks for listening :)