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

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

Outcomes