Date Conversion

[knnwndlm]

Hi SME,

I have a strange result from using the DateTime tool.  I have a date column showing up as a string in the format (mm-dd-yyyy).  When I use the DateTool to convert it to the Date/Time format using MM-dd-yyyy, I got the result showing up as yyyy-mm-dd.

For example, the string input is '03-05-2018' prior to using the DateTime tool. However, it shows up as '2018-03-05' after using the tool.

Could someone please help me understand why this is the case?  I can't use the Select tool either because it would come out as null.  Any suggestions on how to approach this?  

Thanks,

Konn

[Felipe_Ribeir0]

Hi @knnwndlm 

This is the result because the default pattern for Alteryx dates is 'yyyy-mm-dd'. So with this tool you are converting a string ('mm-dd-yyyy') into a date. This conversion is useful because after that, you will be able to use date functions with formulas to manipulate your dataset.

When you try to convert the string ('mm-dd-yyyy') into a date directly using the select tool the result is null because you are trying to convert a string that is not in the default date format that Alteryx understand. You must use the datetime tool to convert it.

After finishing the transformations on your data set, you can convert the 'yyyy-mm-dd' format back into 'mm-dd-yyyy' using the DateTimeFormat (on formula tool) function, if its necessary.

Take a look at this recent post, where this same doubt was discussed Solved: Re: HELP!!! I'm studying for the 2nd exam for cert... - Alteryx Community

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[knnwndlm]

Hi @Felipe_Ribeir0!

I'm still unable to get this thing to work.  At the end, I still get the format in 'yyyy-mm-dd'.  What I want is to get 'mm-dd-yyyy' at the very end so that I can do comparisons with other dates.  Any suggestions?

Thanks,

Konn

[Felipe_Ribeir0]

Hi @knnwndlm

With this configuration you are transforming date 'yyyy-mm-dd' into string 'mm-dd-yyyy' right? So, what more than that you need to do?

[Luke_C]

Hi @knnwndlm 

@Felipe_Ribeir0 nailed it. If you're trying to do comparisons/logic/formulas based on date it needs to be in the supported format 'yyyy-mm-dd'. Once you're done with the logic you can format the date however you'd like, but it will be treated as a string that can no longer have date operations ran on it. 

Perhaps you can share a bit more of your use case? Example inputs/expected output?

[binuacs]

@knnwndlm One way of doing this with the DateTimeFormat function and the DateTimeParse function. Attaching a sample workflow for your reference

[knnwndlm]

Hi @Felipe_Ribeir0 , @Luke_C ,

I was hoping to convert everything into 'mm-dd-yyyy' in date before I do the comparisons since the date columns are in the 'mm-dd-yyyy' format.  Otherwise, I'll be looking at two separate formats in the date formats when I'm doing the comparison.  This is simply to facilitate double-checking the results.  Otherwise, it's bit hard to do so.

Thanks,

K

[Luke_C]

Hi @knnwndlm 

DateTime functions will only work with date fields in 'yyyy-mm-dd' format in Alteryx. Any other format is considered as a string data type and would not work with DateTime functions, the most comparison you could do is checking if two fields are the same. The usual practice is to convert date fields to the proper datetime format, do any analysis, then format fields for outputs. 

[knnwndlm]

Thank you @Luke_C!  Did not know that!