Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
=date(Date#(DateField,'DD/MM/YYYY'),'MMM/YYYY')
or
date(Date#(DateField,'DD/MM/YYYY'),'MM/YYYY')
try this
=date(date#(urfield,'MM/DD/YYYY'),'MMM/YYYY') as newdate
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
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
=Date(YourDateField,'MMM/YYYY')
in the main tab
set MonthNames :Jan,Feb,......Dec;
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]