Re: Errors from Informix to SQL7
Informix date columns are encoded as the integer number of days since December 31, 1979 while I'd guess that SQL Server uses MS dates which are number of secs since some other date. If you do not change the default binding Informix will return the internal representation which will not be a valid date on writing it to SQL Server. Try rebinding the column to a string, Informix will convert it to "mm/dd/yyyy" which SQL Server will likely accept. Art S. Kagel smccreadie@my-deja.com wrote: > > Trying to import data from Informix 7 to SQL7 Server using the Informix > 2.80 32-bit ODBC. The imports work OK if their are no date fields. If > the query includes date fields, the import tool on SQL (DTS) gives me a > DirectCopyXForm error for the first row and the import dies. > > I think I need to use a CAST or CONVERT statement on these date > fields. Am I on the right path and how do I do it? This is my first > attempt at working with Informix. > > Sent via Deja.com http://www.deja.com/ > Before you buy.
Art S. Kagel (kagel@bloomberg.net) wrote: : Informix date columns are encoded as the integer number of days since : December 31, 1979 1 meaning 1/1/1900.
Rob Wilson wrote: > > Art S. Kagel (kagel@bloomberg.net) wrote: > : Informix date columns are encoded as the integer number of days since > : December 31, 1979 > > 1 meaning 1/1/1900. Right, sorry not feeling well today, going home. DOS/Win dates are from 1979, arg. Art S. Kagel
The Informix db is production so I can't change the column definitions. Can I do this in code as a query or am I better off writing the data to a text file and importing the text file? (I'm planning to set this up as an hourly extract - 24 times a day) In article <39819E04.D58116C0@bloomberg.net>, kagel@bloomberg.net wrote: > Informix date columns are encoded as the integer number of days since > December 31, 1979 while I'd guess that SQL Server uses MS dates which > are number of secs since some other date. If you do not change the > default binding Informix will return the internal representation which > will not be a valid date on writing it to SQL Server. Try rebinding > the column to a string, Informix will convert it to "mm/dd/yyyy" > which SQL Server will likely accept. > > Art S. Kagel > > smccreadie@my-deja.com wrote: > > > > Trying to import data from Informix 7 to SQL7 Server using the Informix > > 2.80 32-bit ODBC. The imports work OK if their are no date fields. If > > the query includes date fields, the import tool on SQL (DTS) gives me a > > DirectCopyXForm error for the first row and the import dies. > > > > I think I need to use a CAST or CONVERT statement on these date > > fields. Am I on the right path and how do I do it? This is my first > > attempt at working with Informix. > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.
No no. You odn't have to change the table(s), in you ODBC you are binding the date to a date or integer type by default, change the binding to a character field (obviously wide enough) and Informix will automatically convert the date from internal format to a string. Art S. Kagel smccreadie@my-deja.com wrote: > > The Informix db is production so I can't change the column > definitions. Can I do this in code as a query or am I better off > writing the data to a text file and importing the text file? (I'm > planning to set this up as an hourly extract - 24 times a day) > > In article <39819E04.D58116C0@bloomberg.net>, > kagel@bloomberg.net wrote: > > Informix date columns are encoded as the integer number of days since > > December 31, 1979 while I'd guess that SQL Server uses MS dates which > > are number of secs since some other date. If you do not change the > > default binding Informix will return the internal representation > which > > will not be a valid date on writing it to SQL Server. Try rebinding > > the column to a string, Informix will convert it to "mm/dd/yyyy" > > which SQL Server will likely accept. > > > > Art S. Kagel > > > > smccreadie@my-deja.com wrote: > > > > > > Trying to import data from Informix 7 to SQL7 Server using the > Informix > > > 2.80 32-bit ODBC. The imports work OK if their are no date > fields. If > > > the query includes date fields, the import tool on SQL (DTS) gives > me a > > > DirectCopyXForm error for the first row and the import dies. > > > > > > I think I need to use a CAST or CONVERT statement on these date > > > fields. Am I on the right path and how do I do it? This is my > first > > > attempt at working with Informix. > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > Sent via Deja.com http://www.deja.com/ > Before you buy.