Convert string to date field

[GOKULAKANNAN]

I have a date field like 8092021, which is MMDDYYYY, i just need to change this to Mm/DD/YYYY, but problem is some places MM is having single digit like and in some places its having 2 digits , for ex: 12202021, in this case how can define it

[Aurelien_Martinez]

Hi,

You can try :

Date(Date#(Repeat('0', 8-Len('8092021')) & '8092021', 'MMDDYYYY'))

Aurélien

[GOKULAKANNAN]

But in my case , date is a field, how can i apply the same to a field

[Aurelien_Martinez]

With :

Date(Date#(Repeat('0', 8-Len(MyField)) & MyField, 'MMDDYYYY'))

[GOKULAKANNAN]

Can we do the same if the field is number ?

[Aurelien_Martinez]

I think. 

I have tried with this data :

Load
  MyDate,

  Date(Date#(Repeat('0', 8-Len(MyDate)) & MyDate, 'MMDDYYYY'))  as Date
Inline [
MyDate
8092021
12202021
];

[Aurelien_Martinez]

Or you can try with an if

Date(If(Len(MyDate) = 8,
  Date#(MyDate, 'MMDDYYYY'),
  Date#(MyDate, 'MDDYYYY')
)) as Date