topic Re: Date format in QlikView

Date format

ahbzshk07 — Mon, 18 Jan 2016 08:45:47 GMT

Hello Everyone!

How to obtain dates in following format:

1st January 2016

2nd January 2016

3rd January 2016

4th January 2016

Thanks & Regards.

t_chetirbok — Mon, 18 Jan 2016 13:30:38 GMT

Hello!

try this:

day(DATE) &

if(match(day(DATE),1,21,31) , 'st',

if(match(day(DATE),2,22) , 'nd',

if(match(day(DATE),3,23) , 'rd', 'th'))) & ' ' &

MonthName(DATE) & ' ' & year (DATE)

Sure that format LongMonthNames is 'January;February;March;April;May;June;July;August;September;October;November;December';

prieper — Mon, 18 Jan 2016 13:37:16 GMT

I would work with a mapping table:

mapDates:

MAPPING LOAD * INLINE [read, show

1, 1st

2, 2nd

3, 3rd,

21, 21st

22, 22nd

23, 23rd

31, 31st];

and then to use

...

DUAL(APPLYMAP('mapDates', DAY(myDate), DAY(MyDate) & 'th') & ' ' & DATE(myDate, 'MMMM') & ' ' & YEAR(MyDate), MyDate) AS LongDate

HTH

Peter

ahbzshk07 — Mon, 18 Jan 2016 16:26:53 GMT

I thought that we had special format of date to get that st/nd/rd/th part. Anyways, both the answers above are helpful...

Thanks guys for your contribution.