date literals in MDX

I'm wondering if there's a way to put a date literal into an MDX statement. The only way I've been able to figure out to accomplish this is to use the Double datatype equivalent of the date datatype. Any other way?

The reasoning behind this question is that I have a dimension attribute which is a datetime value and has over 250,000 members. I need to select a range of dates, but I can't use the : (colon/range) operator because there's no guarantee that a particular member with the value of midnight will exist. So I have to do a Filter statement on MemberValue. Using a "date literal" improves performance 10x, but the code isn't very readable because that literal is a number, not a date. Compare performance of the following (which is the best I could do to demonstrate against Adventure Works):

select {} on 0,

Filter(

[Date].[Date].[Date].Members

,[Date].[Date].CurrentMember.MemberValue > VBA!DateSerial(2004,7,18)

)

on 1

from [adventure works]

select {} on 0,

Filter(

[Date].[Date].[Date].Members

,[Date].[Date].CurrentMember.MemberValue > 38186

)

on 1

from [adventure works]

The second query performs 10x faster than the first and returns equivalent results. It's just hard to read.

There is no Date literal representation in MDX. It's a nice trick you have with double, but it actually does type conversion (which is still very fast as you discovered). For a more readable, yet performant way - you can use CDate function as following:

select {} on 0,

Filter(

[Date].[Date].[Date].Members

,[Date].[Date].CurrentMember.MemberValue > CDate("7/18/2004")

)

on 1

from [adventure works]

The reason it will work much faster than your first query with DateSerial, is because CDate is one of few VBA functions on the list of.

Now your scenario got me interested - having 250,000 dates is enough to cover about 685 years. But even if this was at the hour granularity, it is still 29 years. And since you say that some of the dates could be missing, it is likely to be at least twice as big (otherwise what is the reason to have missing dates). So what is this company you are working for if it has data from before America was discovered (if day is the granularity) or before computers were invented (if hour is the granularity) ?

|||

Mosha. Thanks. You're absolutely right. It's about 25% faster than the number literals I was using, so that's the best trick.

To fill in your statement about CDate, it's one of the few VBA functions that have internal implementations as specified in Irina's list here:

http://www.e-tservice.com/downloads.html

As for why there are so many, the attribute is actually a datetime attribute, not just a date attribute. (It's got minutes and seconds.)

Thanks again.

Date literals in expressions?

How do I specify a date literal in an expresison? It's not covered in Books Online. None of the following worked:

mydate == '1899-12-30'

mydate == "1899-12-30"

mydate == #1899-12-30#

This did work:

mydate == (DT_DATE) 0

but it's not self-explanatory and it would be utterly stupid if that's the only way to specify a date literal. Are we once again victims of the "rushed-out-the-door" syndrome?

Jamie already experimented on the subject.

This should help you.

http://blogs.conchango.com/jamiethomson/archive/2005/10/11/SSIS_3A00_-How-to-pass-DateTime-parameters-to-a-package-via-dtexec.aspx

Regards,

Yitzhak

|||"12/30/1899"?|||So then I was right, huh? Outside of explicit casting there's no way to deal with date literals? <Sigh> Will SSIS be "fixed" in Katmai? I certainly hope so, although it would be nice if we'd get a service pack for 2005 as well....|||

Phil Brammer wrote:

"12/30/1899"?

It has nothing to do with the date format. When using apostrophes, SSIS complains that the apostrophe is an unexpected character. When using quotation marks, it complains that DT_DATE can't be implicitly converted to DT_WSTR. And number (pound) signs are the syntax for direct references to lineage IDs.

|||I don't know what the issue is.

What are you trying to do?|||

Okay, here's the full story. I'm importing from a dBASE III file. It has a date column. Sometimes the date is NULL, but when you bring "null" dates straight to SQL Server via SSIS (specifically via the Jet OLEDB driver) they do not become NULL but rather are treated as date 0, which equates to 1899-12-30 12:00:00 AM in Jet. I'm trying to test for this value in a Derived Column transformation, hence the need for a date literal. Basically, I wanted to do this:

MyDate | Replace 'MyDate' | MyDate == '1899-12-30' ? NULL(DT_DATE) : MyDate | database date [DT_DBDATE]

But I couldn't figure out a non-casting way to specify a date literal in the expression, hence the question.

This expression worked for me:

MyDate == (DT_DATE) 0 ? NULL(DT_DATE) : MyDate

but I wasn't happy with it.

|||Since SSIS is strongly-typed, and based on a C# syntax, I'm not sure why this comes as a surprise. Personally I prefer it this way. But that's just my opinion |||

The SSIS expression language only has support for numeric, string, and Boolean literals, as documeneted in Books Online - http://msdn2.microsoft.com/en-us/library/a980cd52-54ef-4b9c-b00c-e6807cf8e01f(SQL.90).aspx

sql