Data Pull Filter Error with Fiix Integration (Epoch ms)

Issue:

When using the Epoch (milliseconds) time format in a Fiix Data Pull integration, tasks fail with an "SQLException: Incorrect DATETIME value" error.

Resolution:

Update the request body by replacing the "?" placeholder in the filter with a time shortcut, such as {last1day:epoch_ms}.

When setting up a Data Pull integration specifically with Fiix, you have the option to use the Epoch (milliseconds) time format. This format is great for computers to read, but it can lead to a specific error when you try to use a question mark (?) as a placeholder for dates in your request body filters.

If you are seeing the error "SQLException: Incorrect DATETIME value," it means the connection is having trouble translating that question mark into the numerical format it expects.

This issue occurs because Fiix does not accept a parameter (the ? placeholder) when using the Epoch time format. Even though you may have provided a valid date elsewhere, the system cannot process that date through a parameter when it's expecting an Epoch number.

The Solution

To fix this, you need to remove the question mark (?) from your request body and use a Time Shortcut instead.

These shortcuts are built directly into Fiix. Instead of waiting for an outside value to be passed in, the shortcut tells Fiix to calculate the time itself. This bypasses the need for a parameter entirely and ensures the date is provided in the exact format the system requires.

Updating the Request Body

In your integration settings, locate the filters section of your request body. You will want to swap the manual placeholder for a shortcut.

Example of what to change:
- Incorrect: "dtmDate > ?"
- Correct: "dtmDate > {last1day:epoch_ms}"

Here is how the full request body should look:

						{
  "_maCn" : "FindRequest",
  "className": "YourClassNameHere",
  "fields": "id, dtmDate, strDescription",
  "filters": [
    {
      "ql": "dtmDate > {last1day:epoch_ms} AND intControlID = 1"
    }
  ]
}

Which Shortcut Should You Use?

You can choose a shortcut based on how often your Data Pull integration runs. The key is to include the :epoch_ms tag so Fiix knows to use the millisecond format.

Sync Frequency	Use This Shortcut
Once a Day	{last1day:epoch_ms}
Every Hour	{last1hour:epoch_ms}
Every 15 Minutes	{last15minutes:epoch_ms}