topic Date Format in QlikView

Date Format

Mon, 26 Nov 2012 07:22:13 GMT

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

Re: Date Format

Anonymous — Mon, 26 Nov 2012 07:54:08 GMT

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

Re: Date Format

hic — Mon, 26 Nov 2012 08:19:22 GMT

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

Re: Date Format

its_anandrjs — Mon, 26 Nov 2012 08:48:28 GMT

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

Re: Date Format

Mon, 26 Nov 2012 08:59:07 GMT

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.