convert 18/10/2015 to 10/2015 or OCT/2015

[#1]

hi all,

I have a list of date such as 18/10/2015 and I want to convert them to:

1. 10/2015

2. Oct/2015

can advise, pls?

[sunny_talwar]

Try this:

For 10/2015

Date(Date#(DateField, 'MM/DD/YYYY'), 'MM/YYYY') as NewDateField

Date(MonthStart(Date#(DateField, 'MM/DD/YYYY')), 'MM/YYYY') as NewDateField

For Oct/2015

Date(Date#(DateField, 'MM/DD/YYYY'), 'MMM/YYYY') as NewDateField

Date(MonthStart(Date#(DateField, 'MM/DD/YYYY')), 'MMM/YYYY') as NewDateField

Update: updating my expression because I think Peter is right that we need to use MonthStart or MonthEnd so that we don't see repeating MonthYear on the front end because of the date information that is still intact if we don't use them.

[Peter_Cammaert]

If you plan to use this new field as a dimension to group facts, you may want to use MonthStart().

If OTOH you use simple Date format manipulation, your date values will keep the different days in their underlying numerical representation. A result of which could be that displaying a listbox with values from the new field may list '10/2015' multiple times because they're still different dates. Which is maybe not what you want.

If your default DateFormat string is already set at 'D/MM/YYYY', you can use:

Date(MonthStart(CurrentDate), 'MM/YYYY') AS NewDate // for 10/2015

or

Date(MonthStart(CurrentDate), 'MMM/YYYY') AS NewDate // for Oct/2015

Note that each part before the AS keyword can also be used as an expression in the User Interface, or as a listbox expression.

Best,

Peter

[Kushal_Chawda]

=date(Date#(DateField,'DD/MM/YYYY'),'MMM/YYYY')

or

date(Date#(DateField,'DD/MM/YYYY'),'MM/YYYY')

[Chanty4u]

try this

=date(date#(urfield,'MM/DD/YYYY'),'MMM/YYYY') as newdate

[khadeer]

Hi

     1. Date(Date#(18/10/2015,'DD/MM/YYYY'),'MM/YYYY')

     2. Date(Date#(18/10/2015,'DD/MM/YYYY'),'MMM/YYYY')

Regards,

Khadeer

[Peter_Cammaert]

To make this work, you may need some additional quoting. Otherwise the calculation 18 / 10 / 2015 will become a very small number instead of a date string 

[Anonymous]

=Date(YourDateField,'MMM/YYYY')

in the main tab 

set MonthNames :Jan,Feb,......Dec;

[ahammadshafi]

For 10/2015

Date(MonthStart(Date#(DateField, 'MM/DD/YYYY')), 'MM/YYYY') AS [NewDate]

For Oct/2015

Date(MonthStart(Date#(DateField, 'MM/DD/YYYY')), 'MMM/YYYY') AS [NewDate1]