Re: Datetime data type problem with dbimport.

Posted in 2004

Venkatesh Gopal — Mon, 3 May 2004 08:55:34 -0500 — source: Usenet: comp.databases.informix

--0__=09BBE41ADFDFF7858f9e8a93df938690918c09BBE41ADFDFF785 Content-type: multipart/alternative; Boundary="1__=09BBE41ADFDFF7858f9e8a93df938690918c09BBE41ADFDFF785" --1__=09BBE41ADFDFF7858f9e8a93df938690918c09BBE41ADFDFF785 Content-type: text/plain; charset=US-ASCII Try setting the DBDATE format to MDY4/ and try dbimport again. Venkatesh. |---------+----------------------------> | | "Willie Snyman" | | | <willie@raf.co.za| | | > | | | Sent by: | | | owner-informix-li| | | st@iiug.org | | | | | | | | | 05/03/2004 03:56 | | | AM | |---------+----------------------------> >------------------------------------------------------------------------------------------------------------------------------| | | | To: <informix-list@iiug.org> | | cc: | | Subject: Datetime data type problem with dbimport. | >------------------------------------------------------------------------------------------------------------------------------| Hi there. When I do a dbexport of a database with tables that include datetime data types it works fine. But when I try to use dbimport it stops at the tables with this error message : Non-numeric character in datetime or interval. I've tried to manually load the rows but with no success. In INSERT statement number 1 of raw data file sec_user.unl. Row number 1 is bad. E1141|MARTHIA|M||BRAND|126|6705300011085|27/08/2003|5|1|Y|27/08/2003 14:20:10|||Willie@test| Non-numeric character in datetime or interval. This is the table layout: create table "informix".sec_user ( userid char(8) not null , name varchar(20) not null , initials char(3) not null , nickname varchar(20), surname varchar(20) not null , raf_id_num integer, id_num char(20) not null , date_create date not null , user_stat_cde char(5) not null , user_type_cde char(5) not null , confidential_sign char(1) not null , date_active datetime year to second, date_inactive datetime year to second, user_basket integer, e_mail varchar(20,1) not null , primary key (userid) constraint "informix".pk_sec_user1 ); revoke all on "informix".sec_user from "public"; My environment variables is set to: GL_DATETIME=%d/%m/%Y %H:%M:%S DBDATE=DMY4/ I'me using Informix Dynamic Server Version 9.30.UC2 Any suggestions are welcome. Thanks. --1__=09BBE41ADFDFF7858f9e8a93df938690918c09BBE41ADFDFF785 Content-type: text/html; charset=US-ASCII Content-Disposition: inline <html><body> Try setting the DBDATE format to MDY4/ and try dbimport again. Venkatesh. <img src="cid:10__=09BBE41ADFDFF7858f9e8a93df938@us.ibm.com" width="16" height="16" alt="Inactive hide details for "Willie Snyman" <willie@raf.co.za>">"Willie Snyman" <willie@raf.co.za> <table V5DOTBL=true width="100%" border="0" cellspacing="0" cellpadding="0"> <tr valign="top"><td width="1%"><img src="cid:20__=09BBE41ADFDFF7858f9e8a93df938@us.ibm.com" border="0" height="1" width="72" alt=""> </td><td style="background-image:url(cid:30__=09BBE41ADFDFF7858f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " width="1%"><img src="cid:20__=09BBE41ADFDFF7858f9e8a93df938@us.ibm.com" border="0" height="1" width="225" alt=""> <ul> <ul> <ul> <ul>"Willie Snyman" <willie@raf.co.za> Sent by: owner-informix-list@iiug.org 05/03/2004 03:56 AM</ul> </ul> </ul> </ul> </td><td width="100%"><img src="cid:20__=09BBE41ADFDFF7858f9e8a93df938@us.ibm.com" border="0" height="1" width="1" alt=""> To: <informix-list@iiug.org> cc: Subject: Datetime data type problem with dbimport.</td></tr> </table> Hi there. When I do a dbexport of a database with tables that include datetime data types it works fine. But when I try to use dbimport it stops at the tables with this error message : Non-numeric character in datetime or interval. I've tried to manually load the rows but with no success. In INSERT statement number 1 of raw data file sec_user.unl. Row number 1 is bad. E1141|MARTHIA|M||BRAND|126|6705300011085|27/08/2003|5|1|Y|27/08/2003 14:20:10|||Willie@test| Non-numeric character in datetime or interval. This is the table layout: create table "informix".sec_user ( userid char(8) not null , name varchar(20) not null , initials char(3) not null , nickname varchar(20), surname varchar(20) not null , raf_id_num integer, id_num char(20) not null , date_create date not null , user_stat_cde char(5) not null , user_type_cde char(5) not null , confidential_sign char(1) not null , date_active datetime year to second, date_inactive datetime year to second, user_basket integer, e_mail varchar(20,1) not null , primary key (userid) constraint "informix".pk_sec_user1 ); revoke all on "informix".sec_user from "public";  My environment variables is set to:</