locked
Select with maximum of date in CRM RRS feed

  • Question

  • Hi,

     

    I would like to get something from the CRM-system like the following SQL-Statement:

    SELECT MAX(date), value from table WHERE produktId = 123;

    How would that work?

     

    My code so far:

    ConditionExpression condition1 = new ConditionExpression();

    condition1.AttributeName = "new_produktId";

    condition1.Operator = ConditionOperator.Equal;

    condition1.Values = new String[] { produktId.ToString() };

    ConditionExpression condition2 = new ConditionExpression();

    condition2.AttributeName = "new_date";

    condition2.Operator = ConditionOperator.LessEqual;

    condition2.Values = new String[] { DateTime.Today.ToString() };

    FilterExpression filter = new FilterExpression();

    filter.FilterOperator = LogicalOperator.And;

    filter.Conditions = new ConditionExpression[] { condition1, condition2 };

    QueryExpression query = new QueryExpression();

    query.EntityName = EntityName.new_nvwverlauf.ToString();

    query.ColumnSet = new AllColumns();

    query.Criteria = filter;

     

    I know that gives me all the records in the past. Does anyone have an idea how I should modify the statement to get what I want?

    Thanks for you help,


    Daniel

    Thursday, May 8, 2008 3:43 PM

Answers

  • I found something how it works: (date is here of type CrmDateTime)

     

    DateTime dt = DateTime.Parse(date.Value);

    DateTimeFormatInfo dtfi = new CultureInfo( "en-US", false ).DateTimeFormat;

     

    condition.Values = new String[] { dt.ToString("s", dtfi) };

     

    Regards,

     

    Daniel

    Wednesday, May 21, 2008 8:43 AM

All replies

  • Try using the Orders property to sort by the date and then the PageInfo to limit to only a sinlge record?

    Thursday, May 8, 2008 4:19 PM
    Moderator
  • Thanks, that helped. I looked the orders property in the sdk up and now it works.

    Friday, May 9, 2008 12:06 PM
  • Hi,

     

    I have a problem concerning the issue above. Somehow my date input seems to be wrong. I get the following error message when I want to run the query:

     

    The date-time format for 15.05.2008 15:35:18 is invalid, or value is outside the supported range.

     

    Has anybody some experience with that?

     

    Thanks,


    Daniel

    Thursday, May 15, 2008 1:38 PM
  • All dates passed into CRM must be in UTC format (yyyy-mm-ddThh:mmTongue Tieds), regardless of your localization setttings.

     

    Thursday, May 15, 2008 9:59 PM
    Moderator
  • How can I get the current time in the crmdatetime format so that I can run it in a sql query?

     

    I need it in the following statement:

    condition2.Values = new String[] { ??? };

     

    Thanks,


    Daniel

    Tuesday, May 20, 2008 7:33 AM
  • You will need to format yourself.  The std .Net date formats don't give you the UTC formatting.  But you can build it by using the datapart command.
    Tuesday, May 20, 2008 3:16 PM
    Moderator
  • I found something how it works: (date is here of type CrmDateTime)

     

    DateTime dt = DateTime.Parse(date.Value);

    DateTimeFormatInfo dtfi = new CultureInfo( "en-US", false ).DateTimeFormat;

     

    condition.Values = new String[] { dt.ToString("s", dtfi) };

     

    Regards,

     

    Daniel

    Wednesday, May 21, 2008 8:43 AM