Convert Date to Month-Year format

[surajap123]

Hi All,

I have a date in my fact table in the format DD/MM/YYYY and I want to convert it to Year-Month format MMM-YYYY.  I will be using this new Month-Year field to aggregate my fact table by Month-Year like below.

New:

load *

resident fact

group by MonthYear;

Could you help me how to achieve it.

[Frank_Hartmann]

try this:

date(date#(Datefield,'DD/MM/YYYY'),'MMM-YYYY') as Datefield

[jrepucci15]

Try this:

Month(Date) & '-' & (Year(Date) as YM

[sunny_talwar · Accepted solution]

I would throw in MonthStart in there to make sure that MonthYear value doesn't repeat in a list box because of day

date(MonthStart(date#(Datefield,'DD/MM/YYYY')),'MMM-YYYY')

[surajap123]

Thanks everyone.

Just a final question.

I just checked that MonthName() function is also giving same result as MonthStart() for me. Could you throw some light on this.

[sunny_talwar]

It does give the same output and will be a shorter expression, but I have seen some issues with using MonthName in set analysis when you have to do >= or <= or > or < with it. I prefer to use Date(MonthStart()) because then you can its slightly easier to use. But it comes down to how you plan to use it

[surajap123]

Thanks for clarity.