Convert "YYYYMMDD" format string to date in MDX?

Question

I have some issue with applying date related functions on the "YYYYMMDD" format string in MDX. For example, if I have this query below:

with 
    member foo as WEEKDay("2013-03-21")
select
    foo on 0
from 
    [Some Cube]

It will correctly output "5" for foo in SSMS. But if I change the second line to:

   member foo as WEEKDay("20130321")

Unfortunately, it will throw "type mismatch" error.

So what I want to do is that converting the string to some recognizable date format and then applying the functions on it. Any ideas for the easiest way, e.g. using existing functions?

Please note that the string is actually inputted from members in any cube where the MDX is running on. So the string format could have been recognizable, e.g. "YYYY-MM-DD". So hard coded string converting algorithm may not be ok.

Answer 1

Topic is too old, but maybe this may help someone. Technique is quite brute, but scalable.

with 
    member foo_false as WeekDay("20130321")
    member foo_true as WeekDay("2013-03-21")
    member foo_brute as 
    case when IsError(WeekDay("20130321"))=False then WeekDay("20130321") else
        case
        /* YYYYMMDD */
        when 
        IsError(WeekDay("20130321"))=True AND IsNumeric("20130321")=True
        and IsError(WeekDay(Left("20130321",4)+'-'+Right(Left("20130321",6),2)+'-'+Right("20130321",2)))=False
            then WeekDay(Left("20130321",4)+'-'+Right(Left("20130321",6),2)+'-'+Right("20130321",2))

        /* DDMMYYYY */
        when 
        IsError(WeekDay("20130321"))=True AND IsNumeric("20130321")=True
        and IsError(WeekDay(Right("20130321",4)+'-'+Right(Left("20130321",4),2)+'-'+Left("20130321",2)))=False
            then WeekDay(Right("20130321",4)+'-'+Right(Left("20130321",4),2)+'-'+Left("20130321",2))

        /* MMDDYYYY */
        when 
        IsError(WeekDay("20130321"))=True AND IsNumeric("20130321")=True
        and IsError(WeekDay(Right("20130321",4)+'-'+Left("20130321",2)+'-'+Right(Left("20130321",4),2)))=False
            then WeekDay(Right("20130321",4)+'-'+Left("20130321",2)+'-'+Right(Left("20130321",4),2))

        /* Unsupported Message */
        else "Unsupported Format" end
    end
select
    {foo_false,foo_true,foo_brute} on 0
from 
    [DATA Cube]

Adding supportable formats to the end before "Unsupported", use any input string instead of "20130321".

But the easiest way is to use another layer (e.g. SQL function CONVERT) before inserting to MDX if possible, sure thing.