Date Format

[#1]

How to convert date format like

Datecolumn

january 21, 2011

january 20,2010

march 20,2005

december 5,2012

may 3,2011

may 17,2012

april 3,2006

feb 17,2008

october 16,2012

july 21,2012

into

This type of format  21/01/2011

Thanks

[Anonymous]

One way would be this:

Date((Date#(purgechar(left(Datecolumn,3) & right(Datecolumn,len(Datecolumn)-index(Datecolumn, ' ',1)),' '), 'MMMD,YYYY')),'DD/MM/YYYY') as NewDate

[hic]

Another way would be to use the long month format:

Set LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';

then

Date#(Date,'MMMM DD, YYYY') as Date

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2012/11/05/month-names

HIC

[its_anandrjs]

Hi,

You are able to make a modified date with below script also

Set monthnames as

Set NewMonthNames = 'january;february;march;april;may;june;july;august;september;october;november;december;january';

MakeDate(Right(Datecolumn,4),  Month(Date( Date# ( Trim(Left(Datecolumn,Len(Datecolumn)-7)),'MMMM' ),'MM')), Left(Right(Datecolumn,7),2) ) as NewDate

Regards,

Anand

[#5]

Hi,

you can below script for requiremnet.

MonthNames:
Mapping
LOAD * INLINE [
    MonthName, Value
    january, 01
    feb, 02
    march, 03
    april, 04
    may, 05
    june, 06
    july, 07
    august, 08
    september, 09
    october, 10
    november, 11
    december, 12
];

T:
Load
Date(Date#(MapSubstring ('MonthNames',Date),'mm dd,yyyy'),'dd/mm/yyyy') As Date,
Date As Date_Text
;
LOAD * INLINE [
    Date
    "january 21,2011"
    "january 20,2010"
    "march 20,2005"
    "december 5,2012"
    "may 3,2011"
    "may 17,2012"
    "april 3,2006"
    "feb 17,2008"
    "october 16,2012"
    "july 21,2012"
];

I also attached the sample file. I hope it will solve your problem.

Thanks,

Niranjan M.