Date format

[ahbzshk07]

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]

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 · Accepted solution]

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]

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.