CODEWITHSUNDEEP
sql-server-2012mdxdate-formatdate-conversion
bigbearzhu
bigbearzhu

Reputation: 2441

Convert "YYYYMMDD" format string to date in MDX?

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.

Upvotes: 4

Views: 13268

Answers (3)

Bellash
Bellash

Reputation: 8184

With FooMember being an int here, representing a yyyyMMdd date I could convert it to Date using the following code

   =Format(CDate(Left(CSTR(Fields!FooMember.Value),4)
   + "-" + Mid(CSTR(Fields!FooMember.Value), 5, 2)
   + "-" + Right(CSTR(Fields!FooMember.Value), 2)),
   "dd/MM/yyyy")

use it in a cube using the following code

 Format(CDate(Left([Measures].[FooMember],4)
 + "-" + Mid([Measures].[FooMember], 5, 2)
 + "-" + Right([Measures].[FooMember], 2)),"yyyy/MM/dd")

Upvotes: 0

SouravA
SouravA

Reputation: 5243

The vba function isDate can be used to check if the passed date is well formatted. If not then format it first using dateserial and mid and use them.

with 
member foo as "20130321"

member bar as   
iif(vba!isdate(foo) = TRUE, 
    WEEKDay(foo), //--if the date is already well formatted, it can be used
    WEEKday(vba!dateserial(vba!mid(foo, 0, 4), vba!mid(foo, 5, 2), vba!right(foo, 2))))
select
    bar on 0
from 
    [some cube]

EDIT

The above code can be modified to accommodate other checks such as MMDDYYYY or DDMMYYYY, but the thing is it is impossible in lot of cases for the engine to intuitively know if the passed value is in YYYYMMDDDD or DDMMYYYY or MMDDYYYY. Take for example the string 1111111

This could easily be in any date format as it is a valid date howsoever you break it.

I would suggest that you have another member which can store the date format as well. That way looking at this member, string can be parsed.

e.g. 

with 
member foo as 
//  "20130321" //YYYYMMDD
//  "03212013"//MMDDYYYY
"21032013"//DDMMYYYY

MEMBER dateformat as "ddmmyyyy"



member bar as   
iif(vba!isdate(foo) = TRUE, 
    WEEKDay(foo), 
    IIF(dateformat = "yyyymmdd", //YYYYMMDD
        WEEKday(vba!dateserial(vba!mid(foo, 0, 4), vba!mid(foo, 5, 2), vba!right(foo, 2))), 
        IIF(dateformat = "mmddyyyy", //MMDDYYYY
            WEEKday(vba!dateserial(right(foo, 4), vba!mid(foo, 0, 2), vba!mid(foo, 3, 2))),
            IIF(dateformat = "ddmmyyyy", //DDMMYYYY
                WEEKday(vba!dateserial(right(foo, 4), vba!mid(foo, 3, 2), vba!mid(foo, 0, 2))),
                null
               )
            )
        )
    )

select
    bar on 0
from 
    [aw cube]

Upvotes: 0

Alex Peshik
Alex Peshik

Reputation: 1515

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.

Upvotes: 1

Related Questions