How to clean date format? (URGENT)

[#1]

Hi all,

I have an Excel datasource but in the Transaction Date column, it consists of various date format, for example:

7/12/2010

09-08-10

(Blank)

-

ANy idea how to clean this data?

THanks

[perumal_41]

Hi,

Use this function Date(field1,'DD/MM/YYYY')

[SunilChauhan]

try like below

load

field1

field2

field3

;

;

;

;

date(Datefiled,'DD/MM/YYYY') as date//in place of dd/mm/yyyy you can take ur format

from a.xls

where Datefiled<>' ' or isnull(datefield)=0;

[#4]

Hi,

Please Use Date Function to convert it into the same format and set a default Date for blank Fields:

If(Len(Date)>0,Date(Datefield,'DD-MM-YYYY'),'01-01-2011') as Datefield

Thanks

Vivek

[#5]

Thanks all for the quick reply

I have used both methods but I still hv dirty data

This is my excel data:

TransactionDate
12/12/2009
12/12/2009
-
12/12/2009
31/7/2010
11-07-10
21-07-10
31-07-10
31-07-10
20/7/2010
20/7/2010

After use date(TransactionDate,'DD/MM/YYYY'), I hv this data:

TransactionDate

12/12/2009

12/12/2009

12/12/2009

31/7/2010

11-07-10

21-07-10

31-07-10

31-07-10

20/7/2010

20/7/2010

Why oh why

[SunilChauhan]

use

ltrim(date(TransactionDate,'DD-MM-YY')) as Date

in ur script.

and see the result

[#7]

Hi all,

Actually it works already but the value changed after Date(TransactionDate, 'DD/MM/YYYY')

From 21-07-10 become 10/07/2021

From 31-07-10 become 10/07/2031

[SunilChauhan]

did u dry my post

or

can you share the sample

[#9]

Yes, I did try urs

Pls see the attachment

[SunilChauhan]

use below code and then reload the application

TransactionDate:

LOAD

TransactionDate as OriginalTransactionDate,

Date(ltrim(TransactionDate), 'DD-MM-YY') as NewTransactionDate,

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sheet1);

NewTransactionDate date will show you accurate result

hope this help