CRM 2011/ CRM 2013 oData Error When Passing Dynamic Date as Parameter : Unrecognized 'Edm.DateTime' literal 'datetime'2014-05-12T04:5:28Z'' in '75'

In this link, there is a guide to pass the date parameter to oData method.
http://msdn.microsoft.com/en-us/library/gg328025.aspx#BKMK_WorkingWithDates

Then this is the function

function getODataUTCDateFilter(date) {

 var monthString;
 var rawMonth = (date.getUTCMonth()+1).toString();
 if (rawMonth.length == 1) {
  monthString = "0" + rawMonth;
 }
 else
 { monthString = rawMonth; }

 var dateString;
 var rawDate = date.getUTCDate().toString();
 if (rawDate.length == 1) {
  dateString = "0" + rawDate;
 }
 else
 { dateString = rawDate; }


 var DateFilter = "datetime\'";
 DateFilter += date.getUTCFullYear() + "-";
 DateFilter += monthString + "-";
 DateFilter += dateString;
 DateFilter += "T" + date.getUTCHours() + ":";
 DateFilter += date.getUTCMinutes() + ":";
 DateFilter += date.getUTCSeconds() + ":";
 DateFilter += date.getUTCMilliseconds();
 DateFilter += "Z\'";
 return DateFilter;
}

Sometimes it works, but sometimes it does not. It depends on the time you send.
Like in my experience, I face this error :

“Unrecognized 'Edm.DateTime' literal 'datetime'2014-05-12T04:5:28Z'' in '75'”If I pass the millisecond as well, it also show error :


Because actually, after I try to access the URL that I build, yes, it result the error.




Here is my URL :

http://localhost:5555/dev/xrmservices/2011/OrganizationData.svc/tfp_rasdiscountrateSet?$select=tfp_DiscountRate,tfp_PeriodMonth,tfp_rasdiscountrateId&$filter=tfp_EffectiveFrom%20le%20datetime%272014-05-12T04:05:28Z%27%20and%20tfp_EffectiveTo%20ge%20datetime%272014-05-12T04:5:28Z%27%20and%20tfp_PeriodMonth%20eq%2024

Then, I try to another time, it is actually works, then I found out, actually it works if the hours is more than 1 character, for example : ‘2014-05-12T04:55:28Z’

Actually, CRM only accept this format :
YYYY-MM-ddTHH:mm:ssZ

CRM cannot accept this format :
YYYY-MM-ddTH:m:sZ

So, you have to have H, m, and s in two characters format.
And you also do not need to pass until millisecond, it will not work!

If it is 10:8:8, then have to change to 10:08:08
In my case, I have to convert to ‘2014-05-12T04:05:28Z’’2014-05-12T04:55:28Z’’2014-05-12T04:5:28Z’’

Let me try the URL

http://localhost:5555/dev/xrmservices/2011/OrganizationData.svc/tfp_rasdiscountrateSet?%20$select=tfp_DiscountRate,tfp_PeriodMonth,tfp_rasdiscountrateId&$filter=tfp_EffectiveFrom%20le%20datetime%272014-05-12T04:55:28Z%27%20and%20tfp_EffectiveTo%20ge%20datetime%272014-05-12T04:05:28Z%27%20and%20tfp_PeriodMonth%20eq%2024

And it works



Resolution :

Change your getODataUTCDateFilter() function to :

function getODataUTCDateFilter(date) {
    var monthString;
    var rawMonth = (date.getUTCMonth() + 1).toString();
    if (rawMonth.length == 1) {
        monthString = "0" + rawMonth;
    }
    else { monthString = rawMonth; }

    var dateString;
    var rawDate = date.getUTCDate().toString();
    if (rawDate.length == 1) {
        dateString = "0" + rawDate;
    }
    else { dateString = rawDate; }

    var hourString = date.getUTCHours().toString();
    if (hourString.length == 1)
        hourString = "0" + hourString;

    var minuteString = date.getUTCMinutes().toString();
    if (minuteString.length == 1)
        minuteString = "0" + minuteString;

    var secondString = date.getUTCSeconds().toString();
    if (secondString.length == 1)
        secondString = "0" + secondString;

    var DateFilter = "datetime'";
    DateFilter += date.getUTCFullYear() + "-";
    DateFilter += monthString + "-";
    DateFilter += dateString;
    DateFilter += "T" + hourString + ":";
    DateFilter += minuteString + ":";
    DateFilter += secondString + "Z'";
    return DateFilter;
}

Hope it helps!