1 Reply Latest reply on Jan 22, 2014 9:59 AM by Joshua Milligan

    Format Dates using Calculated Field


      I want to show only the month and year from a dimension I have derived.  I can get this by formatting the dimension under properties.  I am looking for another way to do this (mostly because I am stubborn).  I can use "datetrunc" or "datepart", but can only get the month OR the year.  What is the syntax to get both?


      DATEpart('month',[SubscriptionStartDate])    = "January"

      DATEpart('year',[SubscriptionStartDate])       = "2013"


      I am trying to write formula to get result of "January 2013"

      Also, is there a way to get result of "Jan 2013"   or a MMM 'YY format?

        • 1. Re: Format Dates using Calculated Field
          Joshua Milligan



          If you really want to, you could just combine the two lines above to get a string:

          STR(DATEpart('month',[SubscriptionStartDate])) + " " + STR(DATEpart('year',[SubscriptionStartDate]))


          LEFT(STR(DATEpart('month',[SubscriptionStartDate])), 3) + " " + STR(DATEpart('year',[SubscriptionStartDate]))

          But, even better, why not do what you mentioned?  Change the default format of the date to MMM 'YY

          Also, you can create a custom date by right clicking a date field, select "Create Custom Date'.  Then select "Months" as "Date Value" and then apply the custom formatting.  You'll still have a nice date field that still has all the benefit of the hierarchy (but truncated to the month level) and you'll have the format you want.