How to read 'DateTime Hour to Minute' field from Informix Table

[addsd]

Hi,

I have created a linked server from my SQL 2000 with Informix. I'm using OPENQUERY statement to retrive the data from linked server's table. But I'm getting an error while I excute the SELECT statmement and I understand the error. Becasue that error happens after the adding of a particular field and ait works well after exclding that particual filed.

The datatype of that field in Informix is 'DateTime Hour to Minute'

Please find below the query which runs without an error:

SELECT * FROM OPENQUERY(LinkedServer,'Select Tr_Date,fld2,fld3 from table)

The query which has error:

SELECT * FROM OPENQUERY(LinkedServer,'Select Tr_Date,Tr_Time,fld2,fld3 from table)

How can I convert an Informix 'DateTime Hour To Minute' filed type to DateTime type.

Hope that someone can help/advise me on this matter.

Thanks & regards,

Ahmed JEwahar
ajewahar@sa.dhl.com

 

[RickCole]

Not sure what kind of values you have in the field TR_Time but I would imagine if you had a look at the convert function in BOL it might get you somewhere. This is an example of how you could use it:

SELECT * FROM OPENQUERY(LinkedServer,'Select Tr_Date,convert(varchar(8)Tr_Time,108),fld2,fld3 from table)


Rick.

 

[addsd]

Rick.

Thanks for your repsonse.

I tried.... but it doesn't work. Tr_Time filed contain date and time ..example, 10/22/01 22:30:10:00

Appreciate your further help on this.

Thanks & regards,

Ahmed Jewahar
ajewhar@sa.dhl.com

 

[RickCole]

Ahmed try this:

SELECT * FROM OPENQUERY(LinkedServer,'Select Tr_Date,convert(varchar(20)Tr_Time,01),fld2,fld3 from table)

What error message do you get if this also fails.

Rick.

 

[addsd]

Rick,

Thanks for your response.

I tried with above. Still I'm getting the same error message. Kindle see below the error message.


"Error Converting Data type DBTYPE_DBTIMESTAMP to DateTime"

Please help!.

Thanks & regards,
Ahmed
ajewahar@sa.dhl.com

 

[RickCole]

For some reason SQL server can't handle the conversion. Let's just try and convert Tr_Timefrom to character data and see what results are returned:

SELECT * FROM OPENQUERY(LinkedServer,'Select Tr_Date,convert(varchar(20)Tr_Time,01),fld2,fld3 from table)

Rick.