Retrieving the data by passing date and time attribute using Web Api

(0) Share

Report

Posted on by Seenivasan M

307

Hi Experts,

We have fields called start time and End Time(Date and Time).when ever user entering time sheet record with start time and end time ,we need to restrict if time overlapping with existing records.I have tried with filter query(Web API) for matching records to find duplication,but i am not able to get records for date and time field.

Please help me on this to resolve the issue.

Thanks in advance.

Best Regards,
Seenivasan

*This post is locked for comments

I have the same question (0)

All responses (24)

Answers (4)

Gopalan Bhuvanesh 11,401 on at

Like (0)

Report

Hi

Would you please the paste the code here?

Was this reply helpful? Yes No
Seenivasan M 307 on at

Like (0)

Report

Hi,

Please find my code below,

var startTime = Xrm.Page.getAttribute("new_starttime").getValue();

var GetDataQuery = "new_timesheets?$select=new_starttime,new_endtime&$filter=new_starttime ne null and new_endtime ne null and new_starttime eq " + startTime + ""

Was this reply helpful? Yes No
Suggested answer

Nithya Gopinath 17,078 on at

Like (0)

Report
Hi Sreenivasan,

In order to query date time fields, it should be in ISO string format. Try the code below.

Try the code below.

var startTime = Xrm.Page.getAttribute("new_starttime").getValue(); var startTimeISO = startTime.toISOString(); var startTimeFormat = startTimeISO .replace(startTimeISO .substring(19, 23), ""); var GetDataQuery = "new_timesheets?$select=new_starttime,new_endtime&$filter=new_starttime ne null and new_endtime ne null and new_starttime eq datetime'" + startTimeFormat + "'";

See: msdn.microsoft.com/en-us/library/gg309461(v=crm.7).aspx#BKMK_filter

Hope this helps.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Refer this:

http://missdynamicscrm.blogspot.sg/2014/05/crm-2011-crm-2013-odata-datetime.html

Was this reply helpful? Yes No
Verified answer

Gopalan Bhuvanesh 11,401 on at

Like (0)

Report

Hi

The following format works.

You need to convert all the dates to UTC (Z).

https://<orgname>.api.crm4.dynamics.com/api/data/v8.2/accounts?$filter=createdon%20ge%20(2017-05-15T08:00:00Z)%20and%20createdon%20le%20(2017-05-15T08:54:00Z)

Refer the following as well:

missdynamicscrm.blogspot.co.uk/.../crm-2011-crm-2013-odata-error-when.html

Was this reply helpful? Yes No
Seenivasan M 307 on at

Like (0)

Report

Hi Nithya Gopinath,

Thank you for your quick response.

I have tried your code and i am getting null response from web Api request.

Err message: failed to load resource the server responded with a status of 404(bad request)

Was this reply helpful? Yes No
Gopalan Bhuvanesh 11,401 on at

Like (0)

Report

Hi

What is the value for GetDataQuery you are getting, when debugging?

Was this reply helpful? Yes No
Seenivasan M 307 on at

Like (0)

Report

Gopalan Bhuvanesh,

Please find the value of GetDataQuery below,

"new_entity2s?$select=new_starttime,new_endtime&$filter=new_starttime ne null and new_endtime ne null and new_starttime eq datetime'2017-05-09T02:30:00Z'"

Thanks and Regards,

Seenivasan

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Is you entity name new_entity2s ?? Also please make sure you have data for above mentioned start time. I would recomment you to use ge operator.

"new_entity2s?$select=new_starttime,new_endtime&$filter=new_starttime ne null and new_endtime ne null and new_starttime ge datetime'2017-05-09T02:30:00Z' and new_endtime le datetime'2017-05-09T02:30:00Z'"

Was this reply helpful? Yes No
Gopalan Bhuvanesh 11,401 on at

Like (0)

Report

Hi

Try the following directly in the browser and test.

Change the root url to your organization (both <orgname> and <crm>):

https://<orgname>.api.<crm>.dynamics.com/api/data/v8.2/new_entity2s?$select=new_starttime,new_endtime&$filter=new_starttime ne null and new_endtime ne null and new_starttime eq (2017-05-09T02:30:00Z)

Was this reply helpful? Yes No