Trouble with DateTime Tool

[Adam_B]

Hello, 

I have a Date field that I need to convert to a short date as it is not flowing throughout the workflow.  

The date appears as shown below: MM/dd/YYYY hh:mm:ss in the APH REQ Action date field. 

I am using the DateTime tool:

My new field is all NULLS

Not sure I know why. 

Thanks, 

Adam 

[DanielG]

@Adam_B   I think because you dont have seconds in there.  Try custom with this:  

[DanielG]

My results come through with this alteration.  :)

[DanielG]

Check this post out for converting AM/PM to correct hours, as the conversion above doesnt account for that difference.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-12-hour-to-24-hours/td-p/76025...

[Adam_B]

@DanielG That worked, but I want to the Output field to be MM/dd/YYYY without the time. 

[Adam_B]

@DanielG Actually the Year is first then the month, then the time. 

[DanielG]

Alteryx date fields are YYYY-MM-DD so when you use the Date/Time tool that will be your output.  You can just drop the time with a following select tool where you change Data Type from DateTime to just Date.  😀

If you just want to cut the time off but leave it as a string, then use text to columns as such...

and drop the 2nd column by deselecting it with a select tool.

[Adam_B]

@DanielG ok, I will try that, but why is the output backwards. 

[DanielG · Accepted solution]

Because Alteryx uses YYYY-MM-DD as their standard date structure.  So when the DateTime Tool asks for your input string format it knows where to find the bits and pieces of the data to construct an actual date/time field for you.  You can do it with a bunch of formulas, but it is much easier to use the DateTime Tool.  Otherwise your date field is just a string that Alteryx would be able to perform date calculations on.

[Adam_B]

@DanielG Thank you!  That all worked and I am good, I appreciate your time. 

Adam