Results 1 to 5 of 5

Thread: Ole dates

  1. #1
    Join Date
    Mar 2003
    Location
    Johannesburg, South Africa
    Posts
    2

    Unanswered: Ole dates

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Ole dates

    Originally posted by Jonathana
    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.

  3. #3
    Join Date
    Mar 2003
    Location
    Johannesburg, South Africa
    Posts
    2
    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)

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Originally posted by Jonathana
    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.

  5. #5
    Join Date
    Mar 2005
    Posts
    3

    same problem im having

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •