“Invalid Date Format” 使用ODBC连接到FileMaker Pro数据库。

huangapple go评论60阅读模式
英文:

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

问题

我正在编写一个使用ODBC连接到FileMaker Pro数据库的SSIS包。提取过程返回错误消息“ODBC Driver 11 for SQL Server] 日期格式无效”,数据(根据预览按钮)为“2019年4月1日下午12:51:38”。SQL Server将其视为有效日期,但ODBC不满意。驱动程序对什么感到不满意?

英文:

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?

答案1

得分: -1

错误消息"ODBC Driver 11 for SQL Server]Invalid date format"表明ODBC驱动程序在解释FileMaker Pro数据库提供的日期格式时出现了问题。

日期格式"4/1/2019 12:51:38 PM"确实是SQL Server的有效日期和时间格式,但似乎ODBC驱动程序期望的格式不同。ODBC驱动程序通常遵循ANSI SQL标准的日期和时间格式,datetime值的格式为"YYYY-MM-DD HH:MI:SS"。在此格式中,日期和时间之间用空格分隔,时间以24小时制表示。

您可以修改您的SQL查询或在SSIS中进行转换,以使日期格式与ODBC驱动程序期望的格式相匹配。

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

或者,您可以更改FileMaker Pro数据库中的日期格式以匹配ANSI SQL标准。

英文:

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.

huangapple
  • 本文由 发表于 2023年7月13日 23:41:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76681197.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定