Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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
0
elanders1
Asked:
elanders1
1 Solution
 
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:
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
 
elanders1Author Commented:
Works now
0
 
Arvo PaukkunenCommented:
Why you are using mid 22,10? shouldn't it be 19,10?
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now