Converting Date

[EvansM]

Hello everybody,

I need help to convert a date as shown below. I tried to use DateTime tool didn't work, maybe because the the input column is STRING data type.

252014 -------->140205

10312013---------> 131031

1082014-------------->141008

5302014------------>140530

expected results is in RED

Thanks

[RolandSchubert]

Hi @EvansM ,

my first idea was, that a few string operations should do the job using appropriate conditions (last 4 characters = year, first or first two characters = month, remaining characters = day). But - what about e.g. 1122014? Should it be transformed to 140211 or 141201?  I'm not sure, if uniqueness of transformation is possible. Is there a specific rule for dates like this?

Best,

Roland

[afv2688 · Accepted solution]

Hello @EvansM,

Could this help? Your expected output is the format column

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[jmt214 · Accepted solution]

Here's a possible solution. It uses the length of the string and the positioning of the zeroes to detect which transformation to use. I have attached a workflow as well. 

Put this in a formula tool:

IF Length([Input]) = 6
THEN Right([Input],2)+"0"+Substring([Input],0,1)+"0"+Substring([Input],1,1)
ELSEIF Length([Input]) = 8
THEN Right([Input],2)+Substring([Input],0,2)+Substring([Input],2,2)
ELSEIF Length([Input]) = 7 and Substring([Input],1,1)="0"
THEN Right([Input],2)+Substring([Input],0,2)+"0"+Substring([Input],2,1)
ELSE Right([Input],2)+"0"+Substring([Input],0,2)+Substring([Input],2,1)
ENDIF

[EvansM]

@afv2688  Your my hero. It works like a champ. 

Thanks a lot.

[EvansM]

@jmt214 Thanks a lot man. I appreciate for the help.