cancel
Showing results for 
Search instead for 
Did you mean: 

Format DateTimeNow or DateTimeToday and formatting multiple date fields in one formula

SOLVED
Highlighted
NJT
Asteroid

I apologize if this is already covered elsewhere, I searched and couldn't find it but got to a solution through some other posts so I figured to make it easier to find in the future when I forget how to do this I'd make this post. There was a post that was similar but they just wanted time removed and the solution presented was simply take the left characters up to the year. I was looking for reformatting the date time to a string to append to the file name. Found the solution through the How To Dynamically name output files after I had solved my initial problem. Anyway in case anyone else looks for formatting the current date (this works for any date field you have as well). The piece I missed when I was using the date time user guide was that I needed the "" around it.

 

DateTimeFormat(DateTimeNow(),"%Y%m%d") you can format it any number of ways this produces 20180309 as the value in the formula tool. You can add / or - or _ in there or move the month day or year around to different positions. The key is this will then make it a string field though not a date. Alternatively you can use the date tool but I find the flexibility of the formula option to be more useful especially when dealing with multiple date fields.

 

If you're also looking like I was for an easier way to convert multiple string fields into date fields without the tool and without creating new fields the Multi-Formula does the trick. One thing that tricked me was the default for this tool only looks at Numeric fields which I missed, change that to All Fields and you'll see your string fields you want to convert to dates. By the way this only works if all the string fields are formatted the same way so for example if you have 20180309 for one and 3/9/2018 for another this doesn't work you'd need another formula tool for each format or at least I do if someone knows how to handle that with one formula tool let me know. Next all you do is change the output type or you can still have it create new fields if you want and then set the expression used below. Here's a screenshot to see the settings.

 

DateTimeFormat(DateTimeParse([_CurrentField_],"%Y%m%d"),"%Y-%m-%d")

 

Capture.JPG

LordNeilLord
Alteryx Certified Partner
Thanks for sharing :)
bhowes11
Atom

quick list of all the available formats/specifiers is available at the link below too! 

https://help.alteryx.com/11.7/Reference/DateTimeFunctions.htm