AnsweredAssumed Answered

Concatenate, STRVAL, and COALESCE re-format dates

Question asked by durniak on Jun 9, 2014
Latest reply on Jun 17, 2014 by TSGal


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


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