locked
Date conversion error in CRM report RRS feed

  • Question

  • I am using SSRS to generate reports from CRM. One of the reports I generate should be divided into sections by a case call date. To do this I create a calculated field in the dataset 'CallDate;' which is defined as;

       =Month(Fields!createdon.Value)&"/"&Day(Fields!createdon.Value)&"/"&Year(Fields!createdon.Value)

    In my development environment this appears to work perfectly well but when I run this in my live environment I get the error message;

        The value expression in field 'CallDate' references a dataset field which contains an error: Conversion from string '13/05/14 08:50' to type date is not valid

    Why? What is this really complaining about?

    Thursday, May 22, 2014 10:17 AM

All replies

  • One possibility is different language settings, with different date formats (e.g. if the date format were dd/MM/yy, then 13/05/14 is invalid).

    It's not clear what data-types your fields are, and hence where the conversion occurs. Is '13 May 2014 08:50' a createdon date from the source data, or a derived value ?

    If you want to just get the date portion of a datetime field, I find it's safer to use dateadd to remove the hour, minute and second parts (I think CRM doesn't store milliseconds), rather than using string manipulation


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, May 22, 2014 3:58 PM
    Moderator
  • Some progress has been made.

    The createdon date is a native date time field in CRM. After some experimentation and a lot of head scratching it appears that when that is retrieved from CRM via FetchXML it appears as a US formatted date regardless of the users locale settings on their machine or browser (possibly understandable as that is happening at the server and SQL tends to have that format). The conversion then seems to be carried out according to the user's locale which is of course UK and not US. That will then crash or not depending on the date in question.

    So when I test it on 12th May it works as 12/5 and 5/12 are both valid dates. When my boss tests it on 13th May it crashes as 13/5 and 5/13 are not both valid dates. When I then debug it on my system usings BIDS it works because it appears that BIDS ignores the locale settings and always uses US formats. For the moment the solution appears to be to explicitly set the report language setting to en-US.

    Friday, May 23, 2014 11:31 AM
  • Yes. Any of the date/time fields in the database will be stored as UTC. The conversion is made when the data is presented to the user based on the user's settings.

    Friday, May 23, 2014 6:40 PM
  • Hi,

    I suspect that you problem is related to two issues:

    1. If you use any date fields returned from fetchxml in date time functions, then you need to use the underlying date time field value rather than the string formatted using the user's locale. You can do this by adding value to end of the field - eg. Fields!createdonValue.Value

    2. If you are using CDate(someStringValue) then you'll need to make sure that the date string is in the locale of the report.

    Hope this helps,

    Scott


    Scott Durow
    Blog www.develop1.net    Follow Me
    Rockstar365
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Sunday, May 25, 2014 4:32 PM
    Answerer