Function: The Format Function

The FORMAT function is used to create strings with mixed content. It takes a format string and a list of parameters to fill in the format specifiers. This function is similar to the C and C++ "sprintf" function and uses the same format specifiers. You can refer to the reference for sprintf for detailed documentation about the format specifiers.

Prototype:

<string> = format(<format string>, var1, var2, ...var-n);

Parameters:

Return Value: Type String: A formatted string according to the format specifiers and the variables.

Commonly Used Format Specifiers:

Note: For variables of the types of date, time, and timestamp needing several format codes for each variable, the best option is to use the %s format specifier, and then use the string function to format the variable individually, using the spec found under that heading below.

Typical Use Cases:

The FORMAT function is commonly used for constructing return values, building SQL statements, creating FileMaker calculations, generating messages, and forming strings for data exports.

Example:

string s1 = "one", s2 = "two";
print format("This is a fixed string with three other strings in it 1:%s, 2:%s, 3:%s", s1, s2, "last is a constant string");

// Output to the console:
// This is a fixed string with three other strings in it 1:one, 2:two, 3:last is a constant string

// Format with dates in d.m.y format.
date today = now();
string formatted = format("Today is %s and tomorrow is %s", string(today, "%d.%m.%Y"), string(today + 1, "%d.%m.%Y"));
print formatted;

// Output to the console:
// Today is 12.11.2018 and tomorrow is 13.11.2018

// Converting SQL dates and using them in the format.
string sqlDate = "2018-11-12";
date myDate = date(sqlDate, "%Y-%m-%d");
print format("The SQL date is: %s, or in US format %s.", string(myDate, "%d.%m.%Y"), string(myDate, "%m/%d/%Y"));

// Output to the console:
// The SQL date is: 12.11.2018, or in US format 11/12/2018.

The FORMAT function allows you to create formatted strings with various data types and is useful for a wide range of applications, including working with dates, generating SQL statements, and formatting messages.

Formatting Date and Time values

For variables of type date, time, and timestamp, they can be formatted using the string function. The string function converts any data type to a string representation, and for date and time types you can have a second parameter: the format string. This adds flexibility to create strings in the desired format, like SQL format or any other format needed.

The following types of format specifiers can be used:

* The specifiers marked with an asterisk (*) are locale-dependent.

** If the timezone cannot be determined, no characters

Using format strings for parsing date, time, and timestamp

When you have a date, time, or timestamp value, it can be parsed using the function's date, time, or timestamp. Those functions convert a string into the appropriate format. Those functions have an optional second parameter: The format string. If the source string is a SQL formatted date, having "%Y-%m-%d" as the second parameter to the date function will parse the date correctly.