AnsweredAssumed Answered

What is the Format for Selecting Data in a Date Range with a Timestamp Field through an ODBC Connection?

Question asked by lperry on Jan 31, 2019
Latest reply on Jan 31, 2019 by lperry


I have a database which I have a solid ODBC connection through. I am using an external program (written in MATLAB) to query data in a FileMaker 17 database. My code allows me to directly pass a query to the driver. So, I make a cursory object with an SQL Query and pass that directly through the driver to the database. The SQL queries in FileMaker seem to have a slightly different syntax than normal SQL queries, which is why I have had to make custom queries. I am trying to find data within a timestamp range. I am able to query a single timestamp using the following format:

 

Select * FROM "Table Name With Spaces" WHERE "String_Field" = 'String' AND "Timestamp_Field" = {01/31/2019 12:09:58}

 

However, I am not able to enter a range such as

 

Select * FROM "Table Name With Spaces" WHERE "String_Field" = 'String' AND "Timestamp_Field" = {01/30/2019 00:00:00...01/31/2019 23:59:59}

 

OR

 

Select * FROM "Table Name With Spaces" WHERE "String_Field" = 'String' AND "Timestamp_Field" = {{01...01}/{30...31}/{2019...2019} {00...23}:{00...59}:{00...59}}

 

I have tried both two and three periods in the ellipses, but neither has worked.

 

I get a syntax error for the two examples with ranges. I have read through the FileMaker 16 scripting documentation, but it does not have anything about creating SQL queries with timestamp ranges in them. Does anyone know how to pass that script into the driver to make it work?

 

Thank you in advance.

Outcomes