OK I realise that tinkering around under the bonnet of Recorder is asking for trouble, especially if one is trying to make it do things it has not been asked to do before, but I thought this was worthy of posting.

At the request of another member of staff I have added an item to the USEABLE_FIELD table to enable the wizard to filter on the TAXON_OCCURRENCE.ENTRY_DATE field. It took a bit of trial and error, but seemed to work correctly. For the sake of clarity here is the record I added:

USEABLE_FIELD_KEY = DSS006280000003                 (obviously amend as appropriate if using this elsewhere)
TABLE_NAME = TAXON_OCCURRENCE
FIELD_NAME = ENTRY_DATE
FIELD_DESCRIPTION = Entry Date
FIELD_TYPE = DATE                                                      No other record in the table has this entry so I had to guess
APPLY_TO = T
SELECTABLE = False
SORTABLE = False
FILTERABLE = True
CALCULATION_SQL = TAXON_OCCURRENCE.ENTRY_DATE

In order to test this I told the wizard to select all taxa and under Additional filters I selected Entry Date is greater that '01/01/2012'

After about 4 minutes the report returned about 126,000 records, from our total dataset of just over 1,000,000. This is a believable figure (we have been busy). So I reported to my colleague that I have cracked it.

Ten minutes later she came back to me with a problem. She had attempted essentially the same query but filtering using the date '31/03/2012', this time the wizard crashed immediately with an error report containing this:

"The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."

This struck me as odd, since the second query should be a subset of the first, so any erroneous records selected by the second query, would also be selected by the first.

Had I had a clearer understanding of what is going on inside Recorder at this point I would probably have spotted the solution straight away, but I had to go through and test a few different scenarios as follows.

Any date between 13/03/2012 and 31/03/2012 causes the same error as above
Any date from 01/03/2012 to 10/03/2012 returns data (although not, as it turns out, quite as expected)
11/03/2012 and 12/03/2012 run through correctly but return no data

As Rolf would say "Have you guessed what it is yet?"

Of course SQL Server defaults to US style dates, so 31/03/2012 is meaningless and as yet we have entered no data in November or December 2012. When I entered the date as 03/31/2012 the query worked perfectly.

So I am feeling both a little silly and rather smug now.

The purpose of this posting has changed since I started writing it (before I found the solution). I was going to ask for help identifying the problem. No need now.

Instead I am asking if there is any way a change to the Recorder UI could fix the problem and allow me to enter the cut-off dates in UK format, because we are bound to forget when we are in a hurry to get statistics out of the database?

Even if no easy solution is available I am posting this to enable others to do what I have done, because I think a lot of people will find it useful. Also if I post it here I know I can find it next time I need to know how its done...