ExecuteSQL function not returning expected results

Questions
1

I have a customers table with 305 records. 303 of them were created on January 9th, 2020 via import. One was created on January 13th via pressing New Record. The final record was created today (February 11th) via a script. I want to be able to use the ExecuteSQL function to find records that were created within a certain time period. (I know, with my current set of records, it isn't very useful, but eventually it will be). Here is my SQL:

ExecuteSQL ( "SELECT Count(*) FROM Customers WHERE z_CreationTimestamp BETWEEN ? AND ?" ; "" ; "" ;
Timestamp ( Date ( 1 ; 1 ; 2020 ) ; Time ( 0 ; 0 ; 1 ) ) ;
Timestamp ( Date ( 1 ; 31 ; 2020 ) ; Time ( 23 ; 59 ; 59 ) ) )

This should return 304, but it is returning 1. The 1 record it is finding is the record created manually on the 13th. The field it is searching on, z_CreationTimestamp, is the field that is automatically created when creating a new table.

I assume it is an error with SQL needing a different timestamp format. But, whatever I've tried, it doesn't seem to work. Anyone know how to use this function to find using a range in a timestamp field?

2

Try using date formats that FMP expects: '11/12/2019'. You can add the time there also.

However, since I prefer standard formats, what I did was to create a micro-service where I can pass the dates in ISO format. I can help you with that if you're interested.

3

This works for me. eSQL should handle timestamps the way you have. Can you verify that z_CreationTimestamp is a timestamp field and that all the records actually have values? Here's what I get:

Screen Shot 2020-02-11 at 12.44.49 PM

2 Likes
4

It was set as a text field, even though this field in every other table is a timestamp field. I feel dumb now :crazy_face: Thanks!

1 Like
5

Happens to all of us. eSQL is an unforgiving stickler for type!

3 Likes
6

This works too with TS fields, if it helps:

SQL-TS
SQL-TS812×434 50.2 KB

2 Likes
7

I tried simply typing it in like that as well. I just posted the example using the Timestamp function because I was certain it was being formatted correctly.

8

Dates can be such a PITA.

So, using a little micro-service helper method I wrote, I also can pass an industry standard ISO 8601 date like this:

2020-02-11 08:15:12

and, using a real Date API, the method returns:

02/11/2020 08:15:23

for FMP's non-standard date format (API also supports time zones and time zone math for different locals if needed. Basically whatever kind of date math or conversion imaginable.)

Easy to use this approach in an FMP script.

It's odd that the ExecuteSQL function can't use ISO dates. Dates returned from Execute don't work with FMP's own date math...

Glad you got it working! :slight_smile: