SQL, including the ExecuteSQL calc function in FileMaker, returns dates in this format: YYYY-MM-DD
a couple of clarifications:
Execute SQL is a script step that looks at an External (ODBC) source and can perform these statements:
If you use the Import Script step, you can use this statement with an External (ODBC) source:
If you are using the function, ExecuteSQL(), then you can SELECT and the result is pushed to a field or variable by Script Set Field (or Set Variable) or pushed by calculation.
If you are using ODBC link "SELECT" will change nothing.
Try to use \"date\"
There's nothing wrong with your basic SQL as the ExeucteSQL can select records in the regular FMP format with no problems; it just can't handle the dates in the format it itself creates (YYYY-MM-DD).
You might want to experiment with a play FMP solution where you can test ExecuteSQL on two- and four-digit years.
HOPE THIS HELPS.
I'm wanting to extract, to a Filemaker table, only data from a certain date. The SQL file is over 400k records. I only need the ones for a given date. Filemaker takes forever to do this with a perform find.
Then you need Import script step (ODBC data) and the SELECT statement. Take the other advice on the format of the date.