Same Date parameter but in different format and dataset

Hi

I have two datasets in a report, one is to Analysing Service cube and one is to a SQL db.
The dataset from the cube, I have a date parameter that is in text format and the value looks like this:
 @ToDateDateValue = [Date].[DateValue].&[2011-07-14T00:00:00]

Now I want to use the same @ToDateDateValue in my SQL db query, but get the following error.
"Conversion Failed When converting date and / or time from character string."

How can I convert an MDX date to a date SQL db Query like? (DateTime 2011-07-14)

Thank you
Elander
elanders1Asked:
Who is Participating?
 
elanders1Author Commented:
Hi
and Thanks for your reply.

Before i added my question here i tryed CONVERT(VARCHAR(19), @ToDateDateValue, 120) but then i get the error message.
"Conversion Failed When converting date and / or time from character string."
I guess it dont like the [Date].[DateValue].&[ part of the MDX date...

Now i did a workaround to solve it. In the SQL db dataset and its parameter option i add this expression to convert it, probebly not the proper way but it works.
=CDate(Mid(Parameters!ToDateDateValue.Value,22,10))

Is there another way of doint it?

Regards
Elander
0
 
Marcin_ZawadzkiCommented:
use CONVERT function with specified format
0
 
jogosCommented:
Syntax can be found on http://msdn.microsoft.com/en-us/library/ms187928(SQL.100).aspx where you have style 126
0
 
elanders1Author Commented:
Works now
0
 
Arvo PaukkunenPrincipal ConsultantCommented:
Why you are using mid 22,10? shouldn't it be 19,10?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.