Same Date parameter but in different format and dataset

Posted on 2011-10-12
Last Modified: 2012-11-07

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
Question by:elanders1
    LVL 3

    Expert Comment

    use CONVERT function with specified format
    LVL 25

    Expert Comment

    Syntax can be found on where you have style 126

    Accepted Solution

    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.

    Is there another way of doint it?


    Author Closing Comment

    Works now

    Expert Comment

    by:Arvo Paukkunen
    Why you are using mid 22,10? shouldn't it be 19,10?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now