2 Replies Latest reply on Jun 17, 2014 10:40 AM by TSGal

    Concatenate, STRVAL, and COALESCE re-format dates



      Concatenate, STRVAL, and COALESCE re-format dates


      FileMaker Pro


      12, 13

      Operating system version


      Description of the issue

      It appears these functions change the date format, which is not expected:

      1. John Renfrew discovered that if you concatenate the date with "nothing", that if becomes the standard date format that we use (MM/DD/YYYY or DD/MM/YYYY or our non-Am cousins):

           SELECT ''||dateCreated // note that's TWO single quotes before the two pipe characters

      2. STRVAL() will convert to "text":

           SELECT STRVAL(dateCreated) // again depending on where you are located in the world you get MM/DD/YYYY or DD/MM/YYYY

      3. COALESCE() is a function that does several things. For dates:

           SELECT COALESCE(dateCreated,'') // note the TWO single quotes after the comma

      See:  https://fmdev.filemaker.com/message/150100#150100

      Steps to reproduce the problem

      run a query

      Expected result

      date format should not change

      Actual result

      dates are re-formatted

      Exact text of any error message(s) that appear



      use other functions

        • 1. Re: Concatenate, STRVAL, and COALESCE re-format dates


               Thank you for your post.

               I am able to replicate how all three functions change the displayed format.  I'm not sure why you would want to concatenate a null string with the field, although that is a nifty way to change the SQL date format to the installed OS country date format.  Regardless, I have sent all three examples to our Development and Testing departments for review.  When I receive any feedback, I will let you know.

               FileMaker, Inc.

          • 2. Re: Concatenate, STRVAL, and COALESCE re-format dates


                 Our Testing department mentioned that the format change may be due to the file's locale setting.  If you go to "File -> File Options -> Text", and select the Data Entry to "Always use file's saved settings", the format should remain as original.

                 FileMaker, Inc.