Convert YYMMDD number into Date

Question

I am experiencing an issue with the DateTime function on Alteryx that I am hoping someone can help with. My data is formatted as numbers, with an example being 220221, representing the date 02/21/22 (mm/dd/yy). Can someone point to how I could convert that number to the date I used in the example? Please see the image below.

I am thinking the problem could be that Alteryx is reading the number as a string, but since I am a rookie, I am not sure what would be the correct data type to format it as before feeding into the DateTime function.

kathleenmonks · Answer

Hi @GAD318,

As @IraWatt mentioned, you would need an uppercase M for months, but the yy will also look for four digits which is also why you're not getting the result you expect. See this blurb from the datetime function documentation:

"Because up to 4 digits are read for the year, formats that are intended to have only 2 digits without a separator, such as a 6-digit date stamp (for example, %y%m%d for data resembling 170522 for May 22, 2017), are still read as 4 digits. To work around this limitation, you can..."

I would recommend adding a formula before that adds some slashes to your string to account for this:

Substring([example],0,2) + "/" + Substring([example],2,2) + "/" + Substring([example],4,2)

binu_acs · Answer

@GAD318 one way of doing this

DataNath · Answer

How does this look @GAD318? There were some extra considerations because of the 6 digit input date but this should work and the formulas will also change the date as they need, format-wise, so you can just keep the input as numeric.

Can also be rolled into one expression for simplicity:

6 digit date to desired output.yxmd

6 digit date to desired output - 1 exp.yxmd