Hi

Can any one help.

I have linked an Informix 7.31 database thru to Sql Server (Linked server - Ole ODBC). This works fine apart from when I try and select a date field from a Informix table thu the Sql Server query analyser. I get the following error

Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

I have tried using the cast function in Sql Server but this did not help. I need to try and format the date to someting that Sql Server understands before it hits Sql Server.

I have quite a large database +- 10000 tables, so I would like to get a generic solution, rather than changing each field as required

Hi,

OK, test the syntax SQL the function "extend()", for example:
colum_datetime="2003-11-05 09:20:33"
select extend(colum_datetime, year to day) from table ....
Result colum_datetime="2003-11-05"

Gustavo.

Hi

I don't think you quite understood my problem. As my system (Informix) is an ole system, I need to be able to execute the queries directly from SQL server. I cannot load the data into another table before pulling it into SQL Server as the data changes all the time and I would rather not create views for every table because of the shear volume of tables.

I some how need to trick the Informix OLE into converting the date into something that SQL Server will understand. Bear in mind that SQL server has a limit on the lowest date (around about 17th Centurary)

Hi Jonathana,

So sorry, maybe help you environmen variable DBCENTURY.

Gustavo.

Hello,

Im having a similar problem. Im converting an informix database to SQL Server and its crapping out on the date. I still have to check the database to make sure the all the data is a valid date first.

heres some of the information that i found about converting dates

DATETIME represents a specific point in time for the era beginning at 1 A.D. It can store anything from just the year all the way to fractions of a second. To the computer, DATETIME is not a single format. Depending upon the requested precision, it can range in size from 2 to 11 bytes.
DATETIME data types are declared like this:

CREATE table test_datetime (mydatetime DATETIME YEAR to YEAR)
CREATE table test_datetime (mydatetime DATETIME YEAR to DAY)
CREATE table test_datetime (mydatetime DATETIME YEAR to FRACTION)
CREATE table test_datetime (mydatetime DATETIME HOUR to FRACTION(5))
This last part indicates the precision. It is <><>in the format of <lowest_granularity> to <highest_granularity>. The granularities determine the level of precision in the DATETIME.

The highest level of granularity would be YEAR to FRACTION, which would show the entire date and time down to the fraction of a second. How fine the FRACTION granularity represents is up to your operating system. Some systems support fractional time to the millisecond, while some cannot handle fractions of a second at all.

The default size of the FRACTION is 3 decimal places. Up to 5 digits of precision are allowed and are specified by the "X" in the FRACTION(X) portion of the specification.

when i went into dbaccess and queried the table it brought back a datetime
with fractional seconds 5 places to the right of the decimal. When i use query analizer in sql server it returns 3 places to right of the decimal.

you have to write some sort of function or SP that will make the data only have 3 points to the right before sending it to SQL Server.

IF ANYONE KNOWS THIS SYNTAX FOR INFORMIX TO MAKE THE FRACTIONAL SECONDS ONLY HAVE 3 PLACES I WOULD APPRECIATE IT.

HOPE THAT HELPED A LITTLE