[teiid-users] sql server time column

[Steven Hawkins]

There are workarounds that can be done (the previous approach was to change time literals to {ts'1900-01-01 <time>}), but I would rather not do them because:

1. customers are unlikely to have this scenario where a datetime artificially represents only time values - we forced the issue with our bqt testing
2. if you just wrap the column in the appropriate expression at the connector level, you miss out on our rewriter enhancements.

----- Original Message -----
From: "Larry O'Leary" <loleary at redhat.com>
To: "Steven Hawkins" <shawkins at redhat.com>
Cc: "Warren Gibson" <wgibson at redhat.com>, "Paul Nittel" <pnittel at redhat.com>, "teiid-users" <teiid-users at lists.jboss.org>, "teiid-dev" <teiid-dev at lists.jboss.org>
Sent: Thursday, July 23, 2009 10:02:58 AM GMT -06:00 US/Canada Central
Subject: Re: [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 at redhat.com>
Red Hat, Inc.