469,081 Members | 1,426 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,081 developers. It's quick & easy.

Trouble with Informix datatypes

Hi everybody!

I'm working on a migration from Informix to SQL Server... I'm doing it
through DTS and ODBC.
What I'm doing is using the DTS, my data origin is tha Informix
database and mi target is the SQL Server database that I just created,
this is via IBM Informix ODBC and I copy the tables... but the results
are something like this:
--INFORMIX--
create table r_cap_dbt_uni
(
num_db smallint ,
cve_uni char (8) ,
fecha_mens datetime YEAR to MONTH ,
valor float ,
primary key ( num_db, cve_uni, fecha_mens ) ,
foreign key ( num_db )
references cat_db_tec ( num_db )
);
-- SQL SERVER --
create table r_cap_dbt_uni
(
num_db smallint ,
cve_uni char (8) ,
***********************************************
fecha_mens smalldatetime ,
valor float ,
***********************************************
primary key ( num_db, cve_uni, fecha_mens ) ,
foreign key ( num_db )
references cat_db_tec ( num_db )
);
as you can see the float and datetime year to month data types change
like the example before.... I changed the "float" datatype manually to
"decimal" datatype... but I don't know what to do with the "datetime
year to month" Informix datatype, I mean, I dn't know why the DTS
changes this datatype to "smalldatetime"... how can I map this? what's
the process?
Thank you very much!
Rodolfo

Oct 31 '06 #1
3 4040
as you can see the float and datetime year to month data types change
like the example before.... I changed the "float" datatype manually to
"decimal" datatype... but I don't know what to do with the "datetime
year to month" Informix datatype, I mean, I dn't know why the DTS
changes this datatype to "smalldatetime"... how can I map this? what's
the process?
Well, there is no "datetime YEAR to MONTH" datatype in MS SQL Server,
so what I can suggest is that you use the datetime data type. As for
the formatting, this does not get stored in SQL Server, but you'll need
to do the formatting when you retrieve the data.

Hope this helps

Louis

Nov 2 '06 #2
>but I don't know what to do with the "datetime year to month" Informix datatype, I mean, I dn't know why the DTS changes this datatype to "smalldatetime". <<

SQL Server only has waht woudl be a TIMESTAMP in Standard SQL. I would
consider using a pair of DATETIME columns (start of month, end of
month) to mimic the Informix type.

Nov 2 '06 #3
The problem is that SQL Server datetime and smalldatetime will hold the day
and time which is not what you are after (I think).

If you just want to store year and month then you can use integer instead
and store it 200611 for instance; however, you won't be able to use date
functions on it like DATEDIFF etc... So, you need to way the pro's and con's
up; if you do store it in a datetime then you need set it to the first day
of the month and midnight - 200611 would become 20061101 00:00:00.000 for
datetime.

HTH

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Rodolfo" <lo*******@gmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
Hi everybody!

I'm working on a migration from Informix to SQL Server... I'm doing it
through DTS and ODBC.
What I'm doing is using the DTS, my data origin is tha Informix
database and mi target is the SQL Server database that I just created,
this is via IBM Informix ODBC and I copy the tables... but the results
are something like this:
--INFORMIX--
create table r_cap_dbt_uni
(
num_db smallint ,
cve_uni char (8) ,
fecha_mens datetime YEAR to MONTH ,
valor float ,
primary key ( num_db, cve_uni, fecha_mens ) ,
foreign key ( num_db )
references cat_db_tec ( num_db )
);
-- SQL SERVER --
create table r_cap_dbt_uni
(
num_db smallint ,
cve_uni char (8) ,
***********************************************
fecha_mens smalldatetime ,
valor float ,
***********************************************
primary key ( num_db, cve_uni, fecha_mens ) ,
foreign key ( num_db )
references cat_db_tec ( num_db )
);
as you can see the float and datetime year to month data types change
like the example before.... I changed the "float" datatype manually to
"decimal" datatype... but I don't know what to do with the "datetime
year to month" Informix datatype, I mean, I dn't know why the DTS
changes this datatype to "smalldatetime"... how can I map this? what's
the process?
Thank you very much!
Rodolfo

Nov 2 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Christian Eriksson | last post: by
10 posts views Thread by Joachim Banzhaf | last post: by
14 posts views Thread by Khan | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.