AnsweredAssumed Answered

ExecuteSQL issues with Timestamp fields.

Question asked by AnthonyDellos on Oct 1, 2012

Summary

ExecuteSQL issues with Timestamp fields.

Product

FileMaker Pro

Version

12.0v2

Operating system version

OS X Mountain Lion 10.8.2

Description of the issue

ExecuteSQL doesn't return expected records when the WHERE condition contains a Timestamp field that has indexing on.

Steps to reproduce the problem

Go into the "Define Database" window. Set the indexing value for the Timestamp field to 'none'.

Do a query against the field like:

SELECT foo FROM bar WHERE baz > "2012-01-01 00:00:00'

Where baz is a Timestamp field in table bar. Query returns foo for all records with a timestamp value in baz that occurs in the year 2012.

Now set the indexing value for the Timestamp field to 'All'.

Run the query again and you get an empty result.

Expected result

To get the records that match the query independent of the index setting for the Timestamp field being used in the WHERE condition.

Actual result

Empty result if the index value is set to 'All'.

Configuration information

I have a very simple file I can provide that demonstrates this issue if needed.

Workaround

Leave indexing off on the Timestamp field if you want to use it for WHERE conditions in ExecuteSQL.

Outcomes