I have to admit it I have a problem with dates. I always seem to draw a blank when trying to remember them; I’ve outsourced much of my anniversary notification to outlook and my iPhone, or work with them in applications. It has been ironic this week as the last decade ends and the next decade begins that the first thing that stumps me is a little date problem.
A choice of dates
I’ve been doing some development using the Silverlight API and I came to the part of the project where I needed to filter data using a date range and display on the map and in graph (or chart) form. Now querying with dates with any system is always tricky, especially with dates. Getting the right format can be the difference between date success and date fail! The right positioning of brackets or hash (US pound) symbols has often been the bane of my life.
As this is the Silverlight API I’m backing onto the ESRI REST API for my heavy lifting. So I need to know the right REST syntax in order to create a correct query for use both as the ‘where filter’ for the feature layer as well as the ‘where filter’ for the query task. Fortunately the REST API provides a nice query form with which you can enter these parameters to your heart’s content (sample server example link).
Now, at this point you might think my work here is done, that this form and the collective knowledge of the internet (by that I mean my Google search box, to which I have outsourced my ability to remember syntax), would be able to get me to my goal of a working filter and in some ways it did, but then I found that there was a choice.
Looking at the help page for the REST API here we can see the query layer section which gives the possible filters for the whereas ‘any legal SQL where clause operating on the fields in the layer is allowed’. Now in the project that I was working on the dates being queried were in two different layers, one which was being filtered and the one providing the data for plotting on the graph. Both the layers contained one or more fields of the type “esriFieldTypeDate”, so I thought the same query would work on both, wouldn’t it?
Well it turns out that the answer is no. I initially started with one layer and was provided with the date format of #2009/01/01 00:00:00# which worked fine for querying a single field.
In usage this would give a query in the format of
"FIELDNAME > #2009/01/01 00:00:00#";
But when I applied this to another layer where I was filtering data by two date fields I got the strange error of:
“Unable to perform query. Please check your parameters.”
Hmm I thought, but it worked on the other layer (which is synonymous to the cry ‘but it worked on my machine’) so doing a bit of googling I managed to turn up a link on the forums where someone had had a similar problem and a suggestion to use the following syntax in the where clause:
"FIELDNAME > DATE ‘2009/01/01 00:00:00’";
This worked for both sets of queries. Whether it will work for all databases or data sets will require more testing, but hopefully if your using dates in your application then this way of formatting a query for dates will hopefully work. If I come up with any more definitive answers for which format to use and where or a list of any other date formats that might not work with all datasets then i will endeavour to update this post!
As the first post of the year you might be expecting a string of pointless predictions about cloud computing, three screens and maybe a slate. Unfortunately I’ve yet to obtain the job title of ‘Futurologist’ so I’ll leave that to those sort of people who probably should really be out there doing a real job, like policeman, soldier or plumber, hmm I think I might need to shut up now and slink off with my Solution Architect business cards before someone outs me as a fraud.
Anyway enough of my blathering and hope you have had a happy new year up till now and your date searches will forever be successful.