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

    Concatenate, STRVAL, and COALESCE re-format dates

    durniak

      Summary

      Concatenate, STRVAL, and COALESCE re-format dates

      Product

      FileMaker Pro

      Version

      12, 13

      Operating system version

      All

      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

      none

      Workaround

      use other functions

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

               durniak:

               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.

               TSGal
               FileMaker, Inc.

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

                 durniak:

                 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.

                 TSGal
                 FileMaker, Inc.