ODBC链接表未显示几分之一秒 [英] ODBC linked table not showing fractions of seconds
问题描述
我已通过ODBC连接将IBM notifyix数据库表链接到Access 2010数据库.我的问题是,此表中的日期字段仅在Access视图中显示dd/mm/yy HH:nn:ss
,其中存储的数据为1000秒.
I have linked an IBM informix database table through an ODBC connection to an Access 2010 database. My issue is that the date field in this table only shows dd/mm/yy HH:nn:ss
in the Access view, where the stored data is to 1000th of a second.
我可以在Excel 2010中显示此内容,但不能在Access 2010中显示:这可能吗?没有这么高的准确度会阻止我进行准确的计算!
I can show this in Excel 2010 but not in Access 2010: is this possible? Not having this level of accuracy is preventing me making accurate calculations!
推荐答案
There is a similar question on another forum here. The Date/Time
field type in Access does not store fractions of seconds, and linked tables implicitly cast their columns to the corresponding Access data type, so the fractions of seconds are not available in a linked table even though they are stored in the remote database.
例如,我有一个SQL Server数据库,该数据库具有名为dbo.linkedTable
的表,该表具有一个datetime
列,其中包含几分之一秒:
For example, I have a SQL Server database with a table named dbo.linkedTable
that has a datetime
column with fractions of seconds:
如果我在Access中创建链接表,则[datetimeCol]将映射到Access中的Date/Time
字段类型,并且时间将四舍五入到最接近的秒数
If I create a linked table in Access the [datetimeCol] is mapped to the Date/Time
field type in Access and the times are rounded to the nearest second
作为一种解决方法,我可以创建一个通过查询,该查询使用T-SQL将datetime
值转换为字符串...
As a workaround, I can create a Pass-Through query that uses T-SQL to convert the datetime
value to a string...
SELECT ID, CONVERT(varchar, datetimeCol, 21) AS strDatetime FROM dbo.linkedTable
...返回...
...并且我可以解析[strDatetime]字符串值以检索小数秒.
...and I can parse the [strDatetime] string value to retrieve the fractional seconds.
这篇关于ODBC链接表未显示几分之一秒的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!