Microsoft Dynamics CRM (Archived)

The date-time format for createdon is invalid, or value is outside the supported range

(0) Share

Report

Posted on by Community Member

I want to retrieve all records based on recent similar createdon date. It is showing following exception.

The date-time format for createdon is invalid, or value is outside the supported range

Query:

var d1 = Convert.ToDateTime(subaction["startdate"]);

var d2 = Convert.ToDateTime(subaction["approvedenddate"]);

string FetchXML = "<fetch>" +

" <entity name=\"sales\" >" +

" <attribute name=\"rate\" />" +

" <filter type=\"and\">" +

" <condition attribute=\"date\" operator=\"between\" descending=\"true\" >" +

" <value> " + d1.ToString("MM/dd/yyyy") + " </value>" +

" <value> " + d2.ToString("MM/dd/yyyy") + " </value>" +

" </condition>" +

" <condition attribute=\"createdon\" operator=\"eq\" value=\"createdon\" >" +

" </condition>" +

" <condition attribute=\"ford_approved\" operator=\"eq\" value=\"1\" />" +

" </filter>" +

" <order attribute=\"createdon\" descending=\"true\" />" +

" </entity>" +

" </fetch>";

Please help me with this issue.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Gopalan Bhuvanesh 11,401 on at

Like (0)

Report

Hi

Alex is correct, ((Sorry, I provided for JS).

Please try ISO 8601 format.

d1.ToString("s");

Was this reply helpful? Yes No
Suggested answer

ashlega 34,477 on at

Like (1)

Report

I don't think there it toISOString in c#?

Pretty sure that date format depends on how user settings are configured.. so, if the plugin/workflow is running in the context of a specific user, the date format used for those fetch conditions has to match the date format configured for that user in CRM.

You might try using the approach described here to get user-specific date format from CRM:

bds-experts.com/get-user-personal-settings

And, then, may have to use that format for the data conversions.

Or you might just convert that fetch into a queryexpression - you can use DateTime parameters without "ToString" conversion there:

stackoverflow.com/.../how-to-write-a-between-query-expression-for-todays-date-and-two-date-attribut

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi Priyanka,

Value should be date in "yyyy-MM-dd" format for "createdon" condition.

and one more thing, Please use "on" operator instead of "eq" for date.

For Example, you can use

<condition attribute="createdon" value="2017-05-19" operator="on"/>

Hope this helps

Thanks & Regards

Himanshu Prajapati

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report
Use below function to format your date to yyyy-mm-dd:

function formatDate(date) { var d = new Date(date), month = '' + (d.getMonth() + 1), day = '' + d.getDate(), year = d.getFullYear(); if (month.length < 2) month = '0' + month; if (day.length < 2) day = '0' + day; return [year, month, day].join('-'); }

var d1 = Convert.ToDateTime(subaction["startdate"]); var d2 = Convert.ToDateTime(subaction["approvedenddate"]); d1 = formatDate(d1); d2 = formatDate(d2); string FetchXML = "<fetch>" + " <entity name=\"sales\" >" + " <attribute name=\"rate\" />" + " <filter type=\"and\">" + " <condition attribute=\"date\" operator=\"between\" descending=\"true\" >" + " <value> " + d1 + " </value>" + " <value> " + d2 + " </value>" + " </condition>" + " <condition attribute=\"createdon\" operator=\"eq\" value=\"createdon\" >" + " </condition>" + " <condition attribute=\"ford_approved\" operator=\"eq\" value=\"1\" />" + " </filter>" + " <order attribute=\"createdon\" descending=\"true\" />" + " </entity>" + " </fetch>";

Was this reply helpful? Yes No
Abdul Wahab 12,119 Moderator on at

Like (0)

Report

Hi Gopalan and Alex

Thank you so much guys.

Regards,

AW

Was this reply helpful? Yes No