Date Problem

[#1]

Hi,

I have a date column which is from excell file where date is not in proper order like

DateCol:

APR

MAY

1997

36319

36901

18/11/2000

36893

14/10/2002

38821

39850

20/09/2009

40486

40278

2003

2001

2004

2005

2006

2009

2010

How to create it in a proper order.

Please help for this it is urgent

Thanks

Ritul

[swuehl]

You should be able to create a solution using alt() function with some possible date interpretation functions.

You'll need to decide how to handle year numbers and months names, what date do you want to assign to these?

A solution could look like this:

Set DateFormat = 'DD/MM/YYYY';

SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec';

LOAD *, alt(if(IsText(DateCol),Date(Date#(DateCol,'MMM')), if(DateCol < 2200,MakeDate(DateCol),Date(DateCol)))) as NewDate INLINE [

DateCol

APR

MAY

1997

36319

36901

18/11/2000

36893

14/10/2002

38821

39850

20/09/2009

40486

40278

2003

2001

2004

2005

2006

2009

2010

];

[#3]

Thanks, I have one question also what is the meaning of if(DateCol < 2200,MakeDate(DateCol),Date(DateCol) )

please explain it.

Thanks,

Ritul

[swuehl]

Just noticed, that you don't really need the alt() function here, reason being that you need to check the numeric value in a condition anyway:

if(IsText(DateCol),Date(Date#(DateCol,'MMM')), if(DateCol < 2200,MakeDate(DateCol),Date(DateCol))) as DateColNew

Thanks, I have one question also what is the meaning of if(DateCol < 2200,MakeDate(DateCol),Date(DateCol) )

please explain it.

If your numeric value is smaller than 2200, I assume it's describing a year (first argument to makedate() function), if it's larger than 2200, it's a date in numeric representation, so we just need to format the text representation.