Date format transformation

[#1]

Dear Community,

I have a field in general format , example:

20 Mar 2012

It is not in date format, so I need to convert this to date format, somehow my script failed to do so.

Appreciate if anyone could help!

Test:

Load

     Left([Added On], 2) as TempDay,

     TextBetween([Added On], ' ', ' ') as TempMonth,

     mid([Added On], 😎 as TempYear

from xxx.xlsx

(ooxml, embedded labels, header is 1 lines, table is Table1);

TempDate:

Load *,

     TempDay & '-' & TempMonth & '-' & TempYear as TempDate    

Resident Test;

Date:

load *,

     date(TempDate) as Date

Resident TempDate;

Drop table TempDate;

Thanks,

Chanel

[#2 · Accepted solution]

Hi Chanel

Maybe you can interpret the string using built-it Date#() instead of manual parsing.

Try this: Date#([Added On], 'dd MMM YYYY')

Lukasz

[Sokkorn]

Hi Chanel,

Use this

Date(Date#([Added On],'dd MMM yyyy')) as TempDay,

Regards,

Sokkorn

[agilos_mla]

Try :

Load Date(Date#([Added On], 'D MMM YYYY'), 'M/D/YYYY')

Michael

[#5]

Hi,

Use this

Date(date#([Added On],'DD-MMM-YYYY'),'DD-MMM-YYYY')

HTH

-Shruti

[#6]

Hi, try the file i attached

[#7]

HI

PFA..Hope this will helps you

[#8]

Thanks for all your helpssss!!!