Query Range of Dates & Time
I am trying to select truck locations based on range of dates and time. I am using an autocomplete form to have user enter a truck ID (Field name = LABEL) and two DatePickers (one for start date/time and one for end date/time). The timestamp is stored in the LOGDT field in the REST service. I understand that I need to convert the dates to string formats, but this is where I am stuck. Where and how do I convert the date formats to string format? Do I assign them in the Form RuntimeModificaitons section or somewhere else? Thank you in advance.
Here are my variables:
strSelectTruck = Truck ID entered by user in the autocomlete box form
dateStateDate = DatePicker1 (start date/time)
dateEndDate = DatePicker2 (end date/time )
strStartDate = String version of dateStartDate
strEndDate = String version of dateEndDate
truckFeatureSet = Where results will be stored from query
Query to select truck based on date range:
"(LOGDT BETWEEN TO_DATE('" + strStartDate + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + strEndDate + "', 'YYYY-MM-DD HH24:MI:SS')) AND LABEL = '" + strSelectTruck + "'"
-
Hi Mark,
I believe I was recently doing something similar with Query Strings. Assuming it really was similar, I had my StartDate and EndDate set as output variables of my FormActivity, I then used an Assign (positioned after my Form) to assign these values to my strStartDate and strEndDate (string variables) using this statement: StartDate.ToString.
-Warren
0 -
Warren,
When I made the changes you suggested, I get the following error when I run the workflow:
"There was a workflow error running activity: Exception has been thrown by the target of an invocation. Workflow 'Truck Location By Date' failed Unhandled exception: 'One or more errors occurred.' in activity '1.109: Truck Query'. One or more errors occurred. Unable to complete operation."
I am not sure where this error is coming from. Is it where I converted my dates to strings or my WHERE clause in the query?
/customer/servlet/servlet.FileDownload?file=00P6000000em1gmEAA
Here's my WHERE statement:
"(LOGDT BETWEEN TO_DATE('" + strStartDate + "', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('" + strEndDate + "', 'YYYY-MM-DD HH24:MI:SS')) AND LABEL = '" + strSelectTruck + "'"
0 -
Hi Mark,
I suspect it's the WHERE clause, what I would do is use the Query Builder in the viewer to create the query you'd like (it will provide a date picker when you select an appropriate field). Once you've created this query, run it with Fiddler running and it will report the query that is being executed against your REST endpoint. By inspecting the webforms tab, I was able to easily figure out to format the date string in order to return results (below). This will help with constructing your query.
/customer/servlet/servlet.FileDownload?file=00P6000000elzniEAA
In my case I had to concatenate a few values together with AssignTasks to create a sensible query (keep in my I only used the year as an output from the form).
Ex.
1. strStartYear = startYear.ToString+"-01-01"
2. strEndYear = endYear.ToString+"-12-31"
3. DateQuery = "(REPORTDATE >= DATE '"+startDate+"') AND (REPORTDATE <= DATE '"+endDate+"')"
-Warren
0 -
Warren,
I appreciate your help. I was curious about what was being written to my strStartDate & strEndDate variables so I set up an Alert to display the variables and it revealed why the query may be failing. However, I don't why the Assign operator is not working properly.
Here is my Select Truck with Date form Sequence:
/customer/servlet/servlet.FileDownload?file=00P6000000em1EDEAY
Here's what is being stored in the dateStartDate & dateEndDate variables. It comes through as expected.
My alert is displaying the following text: "Start Date: " + dateStartDate.ToString + " End Date: " + dateEndDate.ToString
/customer/servlet/servlet.FileDownload?file=00P6000000eltgdEAA
Here's what is being stored in the strStartDate variable after I assigned it to dateStartDate.ToString.
This alert is displaying the following text: strStartDate
/customer/servlet/servlet.FileDownload?file=00P6000000elzWrEAI
Where am I going wrong with the assign function? Somehow strStartDate & strEndDate are not storing the correct values. Once I get this piece figured out I will address the query.
-Mark
0 -
Warren,
I was able to resolve my string variable issue. I moved the Assign function out of the RuntimeModifications. My string value is now being stored as:
/customer/servlet/servlet.FileDownload?file=00P6000000elthCEAQ
Now, I need to work on the query itself. I reviewed your example and came up with statement:
"((LOGDT >= DATE '" + strStartDate + "') AND (LOGDT <= DATE '" + strEndDate + "')) AND LABEL = '" + strSelectTruck + "'"
It is still throwing an error.
-Mark
0 -
Mark,
What Field Type is the field you are attempting to query? In my case it was an actual date field hence why it had to have the DATE prefix prior to the strStartDate. This will likely be different though if it is not a data field.
Also, did you try using the query builder just to see what is being submitted to REST? That may be revealing in terms of the format that you should structure the query.
-Warren
0 -
Resolved.
The key was the fact that we are using 10.22 ArcServer. I converted my DatePicker Long Date & Long Time to strings using these Assign arguments: dateStartDate.ToString("yyyy-MM-dd HH:mm:ss") dateEndDate.ToString("yyyy-MM-dd HH:mm:ss")
Here is my final WHERE statement: "(LOGDT >= date '" + strStartDate + "' AND LOGDT <= date '" + strEndDate + "') AND LABEL = '" + strSelectTruck + "'"
Thanks Warren for your help!
-Mark
0
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
7 Kommentare