December 13, 2014 at 12:27 am
Hi folks,
We have recently imported a database from Informix into 2008 R2, via linked server.
Everything came across perfectly with the exception of Time Interval columns.
Format 00:00:00, hours minutes seconds.
SQL imports this as data type Binary(18), and it displays as below.
0x2000200030003A00330030003A0030003000
Even a straight select to the Informix DB has the same result.
Using a cast in the select to time(7) - or any other type - doesn't work, with errors such as below
Conversion failed when converting date and/or time from character string.
I do see a fair amount of commentary about intervals in SQL, but nothing addressing this particular issue.
Do you know how this can be resolved?
How to import a time interval - hh:mm:ss - into sql correctly from Informix linked server?
Many thanks
December 13, 2014 at 11:49 pm
locus (12/13/2014)
Hi folks,We have recently imported a database from Informix into 2008 R2, via linked server.
Everything came across perfectly with the exception of Time Interval columns.
Format 00:00:00, hours minutes seconds.
SQL imports this as data type Binary(18), and it displays as below.
0x2000200030003A00330030003A0030003000
Even a straight select to the Informix DB has the same result.
Using a cast in the select to time(7) - or any other type - doesn't work, with errors such as below
Conversion failed when converting date and/or time from character string.
I do see a fair amount of commentary about intervals in SQL, but nothing addressing this particular issue.
Do you know how this can be resolved?
How to import a time interval - hh:mm:ss - into sql correctly from Informix linked server?
Many thanks
Quick thought, this is equal to interval data types in Oracle and PostgreSQL, for a time interval the conversion entails converting to nvarchar and then to time, see the snipped
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @INFORMIX_INTERVAL VARBINARY(18) = 0x2000200030003A00330030003A0030003000;
SELECT CONVERT(TIME(0),CONVERT(NVARCHAR(25),@INFORMIX_INTERVAL,0),0);
Result
00:30:00
December 14, 2014 at 2:21 am
Awesome. Thanks
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply