3 Replies Latest reply: Jan 16, 2012 8:29 AM by Henric Cronström RSS

    Date formate



      I have column temp which contain date in 'mm/dd/yyyy'  formate and i  want to change it in this 'Monday1st 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

        • Date formate
          Henric Cronström


          First, add your day numbers as a mapping table:


          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)


            • Re: Date formate

              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.

                • Date formate
                  Henric Cronström

                  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 .