473,238 Members | 1,633 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

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 4257
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
by: John Bentley | last post by:
John Bentley: INTRO The phrase "decimal number" within a programming context is ambiguous. It could refer to the decimal datatype or the related but separate concept of a generic decimal number....
3
by: Christian Eriksson | last post by:
Hi! I'm programming Perl DBI accessing Informix databases. I have a problem on a Solaris machine to use my installed Informix driver (DBD::Informix). I get the following error message: ...
3
by: Lee | last post by:
Hi, I'm developing a socket program to connect to Informix database through the ODBC. In here i called my socket program as "tap" . My tap will listen for data from unix through port 1070. After...
10
by: Joachim Banzhaf | last post by:
Hi, Creating the federated datasource library for informix fails for me. The db2 server is version 8.1 WSE on AIX 4.3. The informix client on this server is version CSDK 2.81/ESQL 9.53 UC2....
0
by: vishalp79 | last post by:
Hi, I am trying to install DBD::Informix module on perl on hpux 11.23 machine. but i get this weird error ...... Set up gcc environment - 4.1.1 *** ExtUtils::AutoInstall version 0.61 ***...
0
by: Lester Knutsen | last post by:
A two-day IBM Informix and DB2 User Group Technical Conference - Friday and Saturday, December 8-9, 2006 We are very please to announce our Keynote Speakers...
14
by: Khan | last post by:
Hello, I want to connect my intranet server (with php/apache) to the other informix database server, But I didnt it. I dont know how i can do it? Can i use php_informix extensions for example...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.