Converting Date

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

Date Time

Data Investigation

Accepted answers

afv2688

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

date_format.yxmd

jmt214

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

Date Parse.yxmd

All comments

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

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

date_format.yxmd

jmt214

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:

Date Parse.yxmd

EvansM

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

Thanks a lot.

EvansM

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703