topic Re: Convert string to date field in App Development

Convert string to date field

GOKULAKANNAN — Mon, 28 Feb 2022 15:09:32 GMT

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

Re: Convert string to date field

Aurelien_Martinez — Mon, 28 Feb 2022 15:16:23 GMT

Hi,

You can try :

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

Aurélien

Re: Convert string to date field

GOKULAKANNAN — Mon, 28 Feb 2022 15:24:05 GMT

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

Re: Convert string to date field

Aurelien_Martinez — Mon, 28 Feb 2022 15:26:11 GMT

With :

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

Re: Convert string to date field

GOKULAKANNAN — Mon, 28 Feb 2022 15:39:23 GMT

Can we do the same if the field is number ?

Re: Convert string to date field

Aurelien_Martinez — Mon, 28 Feb 2022 15:43:49 GMT

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
];

Re: Convert string to date field

Aurelien_Martinez — Mon, 28 Feb 2022 15:48:42 GMT

Or you can try with an if

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