Quantcast

0000-00-00 invalid date ?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

0000-00-00 invalid date ?

ALEX_KOUKLA



 Hallo 
i am trying to import some data from a csv file
i created a foreign table and tried to import the data with
insert into select from statement
but then came this error
"Cannot assign '0000-00-00' to a DATE value: not in format 'yyyy-MM-dd'"
and when i try to skip this field and select all the others i get this message
"Error: From line 1, column 13 to line 1, column 16: Number of INSERT target columns (15) does not equal number of source items (1)"
does that mean that i need to insert every column separately ?
i have some tables with over 50 columns
thank you
and best regards
Alexy Georgos


------------------------------------------------------------------------------
Sell apps to millions through the Intel(R) Atom(Tm) Developer Program
Be part of this innovative community and reach millions of netbook users
worldwide. Take advantage of special opportunities to increase revenue and
speed time-to-market. Join now, and jumpstart your future.
http://p.sf.net/sfu/intel-atom-d2d
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 0000-00-00 invalid date ?

ALEX_KOUKLA
Hallo
i tried to word around this problem by avoiding the columns where the DATE-value 0000-00-00
but now i have another problem :
when i export data from mysql with INSERT INTO OUTFILE the Null values are transformed into \N and i receive this Error
Cannot assign '\N' to a NUMERIC value: NumberFormatException (state=,code=0)
is there a work around in Luciddb or in Mysql so that the Null values become an empty space and not \N ?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 0000-00-00 invalid date ?

ngoodman
Consider reading it in as a string, and then casting to date in SQL.

Generate your original file, including the 0000-00-00.  When you create your BCP make sure you define the datatype for that column to be a CHAR instead of DATE.  

Create a VIEW (or simply change your SELECT statement) that reads in the CHAR, recognizes, the sequence 0000-00-00 and then cast to DATE.

Here's an expression that works...
select c1, case when C1 = '0000-00-00' then null else applib.CHAR_TO_DATE('yyyy-MM-dd', c1) END from
-- my example is hard coded values, you'd select from your CHAR flat file
(values ('0000-00-00'), ('1997-01-01')) as t (c1);

On Aug 25, 2010, at 6:49 AM, ALEX_KOUKLA wrote:

>
> Hallo
> i tried to word around this problem by avoiding the columns where the
> DATE-value 0000-00-00
> but now i have another problem :
> when i export data from mysql with INSERT INTO OUTFILE the Null values are
> transformed into \N and i receive this Error
> Cannot assign '\N' to a NUMERIC value: NumberFormatException (state=,code=0)
> is there a work around in Luciddb or in Mysql so that the Null values become
> an empty space and not \N ?
>
> --
> View this message in context: http://luciddb-users.1374590.n2.nabble.com/0000-00-00-invalid-date-tp5460056p5461341.html
> Sent from the luciddb-users mailing list archive at Nabble.com.
>
> ------------------------------------------------------------------------------
> Sell apps to millions through the Intel(R) Atom(Tm) Developer Program
> Be part of this innovative community and reach millions of netbook users
> worldwide. Take advantage of special opportunities to increase revenue and
> speed time-to-market. Join now, and jumpstart your future.
> http://p.sf.net/sfu/intel-atom-d2d
> _______________________________________________
> luciddb-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Sell apps to millions through the Intel(R) Atom(Tm) Developer Program
Be part of this innovative community and reach millions of netbook users
worldwide. Take advantage of special opportunities to increase revenue and
speed time-to-market. Join now, and jumpstart your future.
http://p.sf.net/sfu/intel-atom-d2d
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...