4:09 p.m.
Several changes in Teiid have made our old behavior of allowing users to mark datatime
columns from SQL Server/Sybase as time type in Teiid not work (it used to only fail in
some situations with prepared statements). The issue is that SQL Server treats the
default year for a time value as 1900 instead of 1970.
My preferred approach is to have users represent the source datetime as a timestamp in
Teiid (which is the default upon import), but perform type reconciliation in a virtual
model using the following expression - convert(timestampadd(SQL_TSI_YEAR, 70, <source
datatime>), time).
To ensure this doesn't harm performance I can add a rewrite rule to perform the
inversion of timestampadd.
Since it includes the time type, this isn't an issue with SQL Server 2008.
Steve
5:02 p.m.
New subject: [teiid-users] sql server time column
On Thu, 2009-07-23 at 10:09 -0400, Steven Hawkins wrote:
Several changes in Teiid have made our old behavior of allowing users
to mark datatime columns from SQL Server/Sybase as time type in Teiid not work (it used to
only fail in some situations with prepared statements). The issue is that SQL Server
treats the default year for a time value as 1900 instead of 1970.
My preferred approach is to have users represent the source datetime as a timestamp in
Teiid (which is the default upon import), but perform type reconciliation in a virtual
model using the following expression - convert(timestampadd(SQL_TSI_YEAR, 70, <source
datatime>), time).
Can this not be done in the connector?
--
Larry O'Leary <loleary(a)redhat.com>
Red Hat, Inc.
5:28 p.m.
New subject: [teiid-users] sql server time column
Is there anyway we can query the SQLServer for how it is basing the
time? If we can, can it be a connector capability?
On Thu, 2009-07-23 at 10:09 -0400, Steven Hawkins wrote:
Several changes in Teiid have made our old behavior of allowing users
to mark datatime columns from SQL Server/Sybase as time type in Teiid not work (it used to
only fail in some situations with prepared statements). The issue is that SQL Server
treats the default year for a time value as 1900 instead of 1970.
My preferred approach is to have users represent the source datetime as a timestamp in
Teiid (which is the default upon import), but perform type reconciliation in a virtual
model using the following expression - convert(timestampadd(SQL_TSI_YEAR, 70, <source
datatime>), time).
To ensure this doesn't harm performance I can add a rewrite rule to perform the
inversion of timestampadd.
Since it includes the time type, this isn't an issue with SQL Server 2008.
Steve
_______________________________________________
teiid-users mailing list
teiid-users(a)lists.jboss.org
https://lists.jboss.org/mailman/listinfo/teiid-users
5390
days inactive
5390
days old
2 comments
3 participants
participants (3)
-
Larry O'Leary -
Ramesh Reddy -
Steven Hawkins