Date query in grid.

I have come across this problem many times. Let's say for example you want to query a table to get all rows in a given date range.

SELECT * FROM PERSON WHERE DateOfBirth >= '01/01/1965' AND DateOfBirth <= '01/01/1970'

The above SQL will work just fine. The problem arises when you try to use a Metastorm grid to do likewise. The where clause is now the Row(s) box on the grid properties, and we would expect something like

(Person.DateOfBirth <= '01/01/1965') AND (Person.DateOfBirth >= '01/01/1970')

to work. But it doesn't.

You can expect an error such as

[Quote] Failed to process 'eExecuteSQL' Metastorm engine Database Connector request. ErrorCode: '-2147217913'. Description: 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'. Source: 'Microsoft OLE DB Provider for SQL Server'. SQL State: '22007'. NativeErrorCode: '242' Procedure GetRecordSet call failed. Position: 411 [/Quote]

The output from SQL profiler (this is a great way to see how Metastorm is actually preparing the SQL, which can in some circumstances be a great way of getting to the crux of a problem should the Metastorm error be too cryptic - although in this example the error message is informative enough) is this :

SELECT Field1,Field2,.... FROM PERSON WHERE (Person.DateOfBirth >= '01/01/1965') AND (Person.DateOfBirth <= '01/01/1970')

Now this looks like it should work, and if you paste it into a query window, it does.

I have to confess, the reason why this fails escapes me, but the workaround works so until we find out why I suggest you just go with it. The workaround is of course to use a date formatter.

We could use

%FormatTime(%dateOfBirth,"dd/mm/yyyy")

but that would still give the same output.

The answer is to format your dates like

%FormatTime(%dateOfBirth,"dd mmm yyyy")

Which in our profiler trace gives you

(Person.DateOfBirth <= '01 jan 1965') AND (Person.DateOfBirth >= '01 jan 1970')

By formatting the dates in this way, you are asking the database engine to do the conversion, which gets us the result we need.