7 Replies Latest reply on Dec 16, 2013 9:31 AM by philmodjunk

    FM13 (earlier?) SQL takes date format as 01/01/2013 (for example)

    JimBessette

      Title

      FM13 (earlier?) SQL takes date format as 01/01/2013 (for example)

      Post

           I fought this evening with trying to write an SQL statement to compare a start and end date.  Everything I could find showed that the SQL should accept something like the following:

           SELECT * from SomeTable WHERE StartDate > '2013-12-31'

           This was not working for me.  I spent a lot of time trying to figure out what was wrong.  Even the FileMakerHacks "missing SQL manual" showed the date in that format.  Almost ready to post here, just for the heck of it, I tried this:

           SELECT * from SomeTable WHERE StartDate > '12-31-2012' AND StartDate < '01-01-2013'   

           To my surprise, it worked!  Then I got even more wild….   … > 12/31/2013 ….  01/01/2013.   It worked!

           Then, off the deep end…  12/31/14 ….   01/01/13.   Yes.  Amazed.  Had to share.  Everyone else who uses FM probably already knows this, but I couldn't' find it ANYWHERE.   Either I am horrible at searching, or it's so well known, that I'm the only one who didn't know.

           Jim

        • 1. Re: FM13 (earlier?) SQL takes date format as 01/01/2013 (for example)
          philmodjunk

               Dates have never worked in SQL for me like that. I've always had to use literal date values as shown in the documentation for SQL.

               In what context are you using SQL? Is this with the ExecuteSQL() function or are you using it to query a remote data source referenced via ODBC or JDBC? Or are you using a plug in for your query?

          • 2. Re: FM13 (earlier?) SQL takes date format as 01/01/2013 (for example)
            JimBessette

                 Well, as you know, I've been working on this database.  It's the same one.  I merely added a new table to it, totally unrelated to any other table.  It's not going to anything remote, just using the tables in this DB.  What I do is type in the SQL into a text field on my layout.  That is then used in a calculation field called result which is:   ExecuteSQL(Query; FieldSeparator;RowSeparator; Parameter1;Parameter2;Parameter3)

                 My query text box is (for example): Select TourCode, StartDate, EndDate, TourComplete from Tour Where Tour.StartDate >'01/01/13'  AND Tour.StartDate < '01/01/14'

                 Since it seems one can't "auto select contents" on a calculated field, I then use a calculation to just copy the result into a text field which is displayed to the user (where auto select works).

            • 3. Re: FM13 (earlier?) SQL takes date format as 01/01/2013 (for example)
              philmodjunk

                   Have you checked to be sure that StartDate is a field of type Date and not a field of type Text? Using leading zeroes like this if that is how your data is entered into the field, would cause text to evaluate correctly with the inequalities, but you'd need the format shown instead of the SQL mandated date format.

                   Or this could be something unique to FMP 13--they might have added support for FMP date formats to make things easier for us. I'd have to fire up FMP 13 and make a few tests to check that...

                   

                        Since it seems one can't "auto select contents" on a calculated field, I then use a calculation to just copy the result into a text field which is displayed to the user (where auto select works).

                   Can you explain what you mean by that? Copying the data to a text field should not be necessary for what you describe, but maybe I am misunderstanding you.

              • 4. Re: FM13 (earlier?) SQL takes date format as 01/01/2013 (for example)
                JimBessette

                     My fields are definitely date fields, not text.

                     

                Can you explain what you mean by that? Copying the data to a text field should not be necessary for what you describe, but maybe I am misunderstanding you.

                     I was wrong on this.  I don't know if I might have changed something, but I thought I wasn't able to get the field to "Select entire contents on entry", but I can.  I recreated the result calculated, un-stored field.   Though there is a penalty.   The field shows highlighted when I enter it, but for about a dozen records, it literally takes 3 seconds when I right click, for the menu from my mouse where Copy is, to show up.  I wonder if it has to re-calculate the field just for the copy.  I may just leave it the way I have it (with the text field that I copy the result into), as it is instantaneous when I click to copy.

                • 5. Re: FM13 (earlier?) SQL takes date format as 01/01/2013 (for example)
                  philmodjunk

                       I waited until today to check back on this as I wanted to be sure that I had time to test some SQL syntax before responding.

                       The correct syntax, by documentation, for referring to a literal date is DATE 'Year-Month-Day'.

                       example: WHERE DateField < Date '2013-12-01'

                       Yet experiments show that:

                       WHERE DateField < '12/01/2013'

                       also works--at least where locality setting support the MM/DD/YYYY format for data entry. I haven't tested to see if those settings affect this at all.

                       And this works the same in both versions 12 and 13.

                  • 6. Re: FM13 (earlier?) SQL takes date format as 01/01/2013 (for example)
                    JimBessette

                         Interesting.  So, I guess if I added the Date "cast" (I call it cast as in say C language) it would have worked as expected in YYYY-MM-DD format.  So, I really did stumble upon the undocumented feature.  :-)   Did you test at all with a Year of 2 digit, rather than 4?

                    • 7. Re: FM13 (earlier?) SQL takes date format as 01/01/2013 (for example)
                      philmodjunk

                           I didn't test that format.