Non-numeric character in datetime
User encountered error 1262 (non-numeric character in datetime) when loading pipe-delimited data. The issue was datetime format mismatch: data used DD-MM-YYYY format (e.g., 17-05-2010) but Informix expected YYYY-MM-DD. Solution: set GL_FORMAT environment variable to '%d-%m-%Y %H:%M:%S' to match the data format, or reformat data to Informix default.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 11.70.FC8W1
HP Intel(R) Itanium 2 9000
HP-UX B.11.31
Hi All,
I am trying to load data into a table from a pipe delimited file using the
"load from <file> insert into" command in dbaccess but get the following error:
1262: Non-numeric character in datetime or interval.
847: Error in load file row 1.
Any help would be appreciated.
Thanks
Flip,
Any chances of showing the first few lines of the data file you are trying to
load? Also the schema of the table.
Regards
Andy G
> To: ids@iiug.org
> From: flipv@raf.co.za
> Subject: Non-numeric character in datetime [34872]
> Date: Thu, 26 Mar 2015 07:25:49 -0400
>
> IDS 11.70.FC8W1
> HP Intel(R) Itanium 2 9000
> HP-UX B.11.31
>
> Hi All,
>
> I am trying to load data into a table from a pipe delimited file using the
> "load from <file> insert into" command in dbaccess but get the following
> error:
>
> 1262: Non-numeric character in datetime or interval.>
> 847: Error in load file row 1.>
> Any help would be appreciated.
>
> Thanks
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
It's likely that the format of the datetime strings in the file so not
match the format Informix is expecting. Post a row or two from the data
(at least the datetime values). The expected format is: "YYYY-MM-DD
hh:mm:ss.fffff" or whatever subset matches the column's resolution.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Thu, Mar 26, 2015 at 7:25 AM, FLIP VAN WYNGAARDT <flipv@raf.co.za> wrote:
> IDS 11.70.FC8W1
> HP Intel(R) Itanium 2 9000
> HP-UX B.11.31
>
> Hi All,
>
> I am trying to load data into a table from a pipe delimited file using the
> "load from <file> insert into" command in dbaccess but get the following
> error:
>
> 1262: Non-numeric character in datetime or interval.>
> 847: Error in load file row 1.>
> Any help would be appreciated.
>
> Thanks
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113f949871b9c505122fc7ee
This is the lines from the load file. It was created on another server using the unload command. 2|1|16|31|0|0.0|17-05-2010 15:33:04|E102302|4|01-12-2014 08:51:32|E2887|\\\\ | 3|1|27|31|0|0.0|17-05-2010 15:33:04|E102302|4|01-12-2014 08:51:32|E2887|\\\\ | 4|1|354|31|0|0.0|17-05-2010 15:33:04|E102302|4|01-12-2014 08:51:32|E2887|\\\\ | Des this mean that I wil have to change the format when I do the unload ?
So that's your problem. The datetime strings are in the wrong format according to the default. You can either modify the data to match the default format (see the SQL Reference Guide manual, the Informix Infor Pages online, or my previous reply for details) or set the environment variable GL_DATETIME to a format appropriate to the data's actual format (see the GLS User's Guide for details on the format for GL_DATETIME and for GL_DATE), such as: export GL_FORMAT='%d-%m-%Y %H:%M:%S' Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 26, 2015 at 8:08 AM, FLIP VAN WYNGAARDT <flipv@raf.co.za> wrote: > This is the lines from the load file. It was created on another server > using > the unload command. > > 2|1|16|31|0|0.0|17-05-2010 15:33:04|E102302|4|01-12-2014 08:51:32|E2887|\\\\ | > 3|1|27|31|0|0.0|17-05-2010 15:33:04|E102302|4|01-12-2014 08:51:32|E2887|\\\\ | > 4|1|354|31|0|0.0|17-05-2010 15:33:04|E102302|4|01-12-2014 08:51:32|E2887|\\\\ > | > > Des this mean that I wil have to change the format when I do the unload ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11346e3cec971a0512300b47
May have bigger issues than the datetime - unless the lines have wrapped in your posting, the format of every other line is different, with a char field in place of the datetime... Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, March 26, 2015 6:21 AM To: ids@iiug.org Subject: Re: Non-numeric character in datetime [34876] So that's your problem. The datetime strings are in the wrong format according to the default. You can either modify the data to match the default format (see the SQL Reference Guide manual, the Informix Infor Pages online, or my previous reply for details) or set the environment variable GL_DATETIME to a format appropriate to the data's actual format (see the GLS User's Guide for details on the format for GL_DATETIME and for GL_DATE), such as: export GL_FORMAT='%d-%m-%Y %H:%M:%S' Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 26, 2015 at 8:08 AM, FLIP VAN WYNGAARDT <flipv@raf.co.za> wrote: > This is the lines from the load file. It was created on another server > using the unload command. > > 2|1|16|31|0|0.0|17-05-2010 15:33:04|E102302|4|01-12-2014 > 2|1|16|31|0|08:51:32|E2887|\\\\ | > 3|1|27|31|0|0.0|17-05-2010 15:33:04|E102302|4|01-12-2014 > 3|1|27|31|0|08:51:32|E2887|\\\\ | > 4|1|354|31|0|0.0|17-05-2010 15:33:04|E102302|4|01-12-2014 > 4|1|354|31|0|08:51:32|E2887|\\\\ > | > > Des this mean that I wil have to change the format when I do the unload ? > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11346e3cec971a0512300b47 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.