[SOLVED] "Invalid Date Format" using ODBC connection to FileMaker Pro database

"Invalid Date Format" using ODBC connection to FileMaker Pro database

I am writing an SSIS package that uses an ODBC connection to a FileMaker Pro database. The extract process returns the error message "ODBC Driver 11 for SQL Server]Invalid date format" and the data (according to the preview button) is "4/1/2019 12:51:38 PM". SQL Server considers this a valid date, but ODBC doesn't. What is the driver unhappy about?

Solution

The error message "ODBC Driver 11 for SQL Server]Invalid date format" indicates that the ODBC driver is having trouble interpreting the date format provided by the FileMaker Pro database.

The date format "4/1/2019 12:51:38 PM" is indeed a valid date and time format for SQL Server, but it seems that the ODBC driver is expecting a different format. ODBC drivers typically adhere to the ANSI SQL standard for date and time formats, which is "YYYY-MM-DD HH:MI:SS" for datetime values. In this format, the date and time are separated by a space, and the time is represented in a 24-hour clock.

You can modify your SQL query or transformation in SSIS to convert the date format to match what the ODBC driver expects.

SELECT
    other_columns,
    CONVERT(DATETIME, '2019-04-01 12:51:38', 120) AS your_date_column
FROM your_table;

Or, you can change the date format in the FileMaker Pro database to match the ANSI SQL standard.