Date formate

[#1]

Hi,

I have column temp which contain date in 'mm/dd/yyyy'  formate and i  want to change it in this 'Monday1^st January 2011'  formate.

I have done every thing apart from this st or rd or th .

How can i apply this string with date.

Thanks in advance

[hic]

First, add your day numbers as a mapping table:

DayNumbers:
Mapping Load * inline
[Day, DayNumber
1, 1st
2, 2nd
3, 3rd
...

You will need 31 rows in this table. Then, you can format the date by using e.g.

dual(WeekDay(DateField) &' '& applymap('DayNumbers', Day(DateField)) &' '& Date(DateField, 'MMM YYYY'), DateField)

[#3]

Thanks for the answer

bt my problem is how to add st or nd or rd.

for it i have used

if(Date(Temp,'dd')=01 or Date(Temp,'dd')=21 or Date(Temp,'dd')=31,'st',if(Date(Temp,'dd')=02 or Date(Temp,'dd')=22,'nd',if(Date(Temp,'dd')=03 or Date(Temp,'dd')=23,'rd','th')))

this statement and save it into a variable but it is not working.

[hic]

I think that the best way to add 'st', 'nd' and 'rd' is to use applymap in the way described above. You could of course instead have a mapping table where 1 is mapped to 'st' only (instead of '1st') but the basic solution will be very similar to the above one.

A nested if-function will also work. But, you should not use the comparison Date(Temp,'dd')=01. The Date function will format the date to (the string) '01', but its numeric value will still be forty thousand something. Use instead: Date(Temp,'dd')='01' (string comparison), or better: Day(Temp)=1 .