Improve ExecuteSQL Date and Timestamp handling

Idea created by Vincent_L on Jun 16, 2018
    Active
    Score5
    • Hemant Kumar Patel
    • isakjorge
    • bigtom
    • Vincent_L
    • Markus Schneider

    Hi,

     

    I'm on a french OS, so filemaker native (as the OS) format for dates are written like this dd/mm/yyyy.

     

    If you want query records matching the dates, you have to query them using filemaker's native format

    But, then filemaker ExecuteSQL returns the dates in the SQL format which is yyyy-mm-dd

     

    So you send dd/mm/yyyy (or yyyy/mm/dd if you've an english OS) and you get yyyy-mm-dd. That's annoying, especially is you want to reuse those returned dates in a new query because ExecuteSQl won't return anything if you use the SQL yyyy-mm-dd format.

     

    Same goes with timestamps

     

    There's 2 ways to fix that :

     

    - Either make ExecuteSQL also work with dates / timestamps submitted in SQL filemaker format (so ExecuteSQL would convert on the fly SQL format  for date/timestamp to the filemaker native format). So you would be able to either write WHERE "date"='dd/mm/yyyy' or "date"='dd/mm/yyyy'

     

    - Or make a new ExecuteSQL function ReturnAsLocalFormat (date / timestamp) ReturnAsLocalFormat SQL / ExecuteSQL function , so you would be able to specify that you want Execute SQL to return date or timestamp in the native format rather than SQL's.

    No, unfortunately, as discussed here ExecuteSQL DateVal is buggy or french documentation is wrong, contrary to what the french documentations reads, DATEVAL('2019-01-30') returns 2019-01-30 and not 30/01/2019. Same goes for TIMESTAMPVAL. those both function should have been delivering ReturnAsNativeFormat would deliver, but right now they seem as useless as f(a)=a