Date & Time Functions

Introduction

These date and time custom functions provide tools for converting between FileMaker date/time formats and ISO 8601 format. ISO 8601 is an international standard for representing dates and times as text, making it essential for data exchange, APIs, and web services.

The functions handle the complexities of parsing and formatting ISO 8601 strings, which can include various precision levels and formats.

These functions were created by Jeremy Bante with contributions from Daniel Smith, as noted in the function comments.

Functions

`Date.FromISO(iso8601Date)`

Description: Parses a date string formatted according to ISO 8601 and converts it to a FileMaker date. For dates with limited precision, the first date in the specified period is returned.

Parameters:

iso8601Date: Text encoding a date in ISO 8601 format (e.g., "2023-04-15")

Return:

A FileMaker date, or an error message if the input isn't a valid ISO 8601 date

Example:

Let (
    [
        $iso = "2023-04-15";
        $date = Date.FromISO($iso)
    ];
    $date
)
// Returns: 4/15/2023 (displayed according to system settings)

Supported Formats:

Calendar dates (YYYY-MM-DD or YYYYMMDD)
Week dates (YYYY-Www-D or YYYYWwwD)
Ordinal dates (YYYY-DDD or YYYYDDD)

`Date.ToISO(theDate)`

Description: Converts a FileMaker date to an ISO 8601 formatted date string (YYYY-MM-DD).

Parameters:

theDate: The FileMaker date to convert

Return:

An ISO 8601 formatted date string, or an error message if the input isn't a valid date

Example:

Let (
    [
        $date = Date(4, 15, 2023);
        $iso = Date.ToISO($date)
    ];
    $iso
)
// Returns: "2023-04-15"

`Time.FromISO(iso8601Time)`

Description: Parses a time string formatted according to ISO 8601 and converts it to a FileMaker time. For times with limited precision, the first time in the specified period is returned.

Parameters:

iso8601Time: Text encoding a time in ISO 8601 format (e.g., "14:30:45" or "T14:30:45")

Return:

A FileMaker time, or an error message if the input isn't a valid ISO 8601 time

Example:

Let (
    [
        $iso = "14:30:45";
        $time = Time.FromISO($iso)
    ];
    $time
)
// Returns: 2:30:45 PM (displayed according to system settings)

Supported Formats:

Basic format (hhmmss) or extended format (hh:mm:ss)
Optional decimal fractions (hh:mm:ss,sss or hh:mm:ss.sss)
Can include "T" prefix (T14:30:45)

`Time.ToISO(theTime)`

Description: Converts a FileMaker time to an ISO 8601 formatted time string (hh:mm:ss or hh:mm:ss,sss).

Parameters:

theTime: The FileMaker time to convert

Return:

An ISO 8601 formatted time string, or an error message if the input isn't a valid time

Example:

Let (
    [
        $time = Time(14, 30, 45);
        $iso = Time.ToISO($time)
    ];
    $iso
)
// Returns: "14:30:45"

`Timestamp.FromISO(iso8601Timestamp)`

Description: Parses a timestamp string formatted according to ISO 8601 and converts it to a FileMaker timestamp. For timestamps with limited precision, the first time in the specified period is returned.

Parameters:

iso8601Timestamp: Text encoding a timestamp in ISO 8601 format (e.g., "2023-04-15T14:30:45")

Dependencies:

Date.FromISO - For parsing the date portion
Time.FromISO - For parsing the time portion

Return:

A FileMaker timestamp, or an error message if the input isn't a valid ISO 8601 timestamp

Example:

Let (
    [
        $iso = "2023-04-15T14:30:45";
        $timestamp = Timestamp.FromISO($iso)
    ];
    $timestamp
)
// Returns: 4/15/2023 2:30:45 PM (displayed according to system settings)

`Timestamp.ToISO(theTimestamp)`

Description: Converts a FileMaker timestamp to an ISO 8601 formatted timestamp string (YYYY-MM-DDThh:mm:ss or YYYY-MM-DDThh:mm:ss,sss).

Parameters:

theTimestamp: The FileMaker timestamp to convert

Dependencies:

Date.ToISO - For formatting the date portion
Time.ToISO - For formatting the time portion

Return:

An ISO 8601 formatted timestamp string, or an error message if the input isn't a valid timestamp

Example:

Let (
    [
        $timestamp = Timestamp(Date(4, 15, 2023), Time(14, 30, 45));
        $iso = Timestamp.ToISO($timestamp)
    ];
    $iso
)
// Returns: "2023-04-15T14:30:45"

`Timestamp.ISO.AddOneSecond(isoTimestamp)`

Description: Adds one second to an ISO 8601 timestamp while preserving the timezone information.

Parameters:

isoTimeStamp: The ISO 8601 timestamp with timezone to be incremented

Dependencies:

Timestamp.FromISO - For converting the ISO timestamp to FileMaker format
Timestamp.ToISO - For converting back to ISO format

Return:

The new ISO 8601 timestamp with the original timezone, incremented by one second

Example:

Let (
    [
        $iso = "2023-10-05T14:48:00-04:00";
        $newIso = Timestamp.ISO.AddOneSecond($iso)
    ];
    $newIso
)
// Returns: "2023-10-05T14:48:01-04:00"

Notes:

The function handles timestamps with and without timezone information
Preserves the original timezone in the output
Useful for incrementing timestamps in API responses or when working with time-sensitive data

Usage Tips

Data Exchange: Use these functions when exchanging date/time data with external systems, especially web services, APIs, and JSON data.
Error Handling: All functions return error messages prefixed with "?" when input is invalid. Check for this prefix when validating results:
Let( [ $result = Timestamp.FromISO($input); $isError = Left($result; 1) = "?" ]; If($isError; "Invalid input"; $result) )
Timezone Awareness: ISO 8601 supports timezone specifications, but these functions currently focus on local time representation. For timezone handling, consider additional processing.
Fractional Seconds: These functions properly handle fractional seconds with comma (European) or period (US) decimal separators in ISO format.
JSON Integration: These functions are particularly useful when working with JSON data that typically uses ISO 8601 format for dates and times.
// Converting JSON date strings to FileMaker dates Let( [ $json = "{\"eventDate\":\"2023-04-15T14:30:45\"}"; $dateString = JSONGetElement($json; "eventDate"); $fmDate = Timestamp.FromISO($dateString) ]; $fmDate )

Date & Time Functions

On this page