Getting this error message "java.lang.NumberFormatException: For input string: "2017-10-25""

[Anonymous]

Hi ,

I have inserted rows in big query  date field with format yyy-mm-dd  when trying to access it talend I am getting this error message .

Starting job RunJobMay01 at 00:05 02/05/2019.

[statistics] connecting to socket on port 4044
[statistics] connected
Running Query : SELECT * FROM `sample-237304.dataset1.sample_emp`
Exception in component tBigQueryInput_1 (RunJobMay01)
java.lang.NumberFormatException: For input string: "2017-10-25"
at sun.misc.FloatingDecimal.readJavaFormatString(Unknown Source)
at sun.misc.FloatingDecimal.parseDouble(Unknown Source)
at java.lang.Double.parseDouble(Unknown Source)
at routines.system.ParserUtils.parseTo_Date(ParserUtils.java:299)
at project1.runjobmay01_0_1.RunJobMay01.tBigQueryInput_1Process(RunJobMay01.java:1497)
at project1.runjobmay01_0_1.RunJobMay01.runJobInTOS(RunJobMay01.java:2633)
at project1.runjobmay01_0_1.RunJobMay01.main(RunJobMay01.java:2482)
[statistics] disconnected

Job RunJobMay01 ended at 00:05 02/05/2019. [exit code=1]

Thanks and Regards,

Aarthi Mohan

[manodwhb]

@maarthi , since you were getting data as 2017-10-25 this,this when you were try to convert to number then you will get this error.

My question is why are you converting to number since the data looks like date data yyyy-mm-DD format?

[vapukov]

Hi,

in 99.99% of cases it is mean - or wrong schema or order of columns in query not the same as in schema 

example:

select
     date,
     int,
     string
from table

with a schema:

int,
date,
string

return similar error

this is why select * not always good approach

[Anonymous]

Hi I am not converting just defined a schema 

my date field column in bigquery has join_dt column with Date data type  -- date '2013-10-11'

In edit schema screen of talend I a created the same field  with Date pattern as "yyyy-mm-dd" 

is that correct please let me know 

or should i make any changes to my data please let me know

Thanks in advance

Screenshot (2).png

[vapukov]

---

@maarthi wrote:

Hi I am not converting just defined a schema 

my date field column in bigquery has join_dt column with Date data type  -- date '2013-10-11'

In edit schema screen of talend I a created the same field  with Date pattern as "yyyy-mm-dd" 

---

but right on your picture - you use MM-dd-yyyy

this is one of the possible reasons - wrong schema

[Anonymous]

Even after removing the date pattern value I face the same issue

Screenshot (3).png

[vapukov]

could you attach few rows from your table?

[Anonymous]

I have attached the Source data which I have used for my mapping .

and my insert query would be 

insert into `sample-237304.dataset1.src_emp1`(`empno`,`firstname`,`lastname`,`location`, `salary`, `join_dt`)values(100,'Teresa', 'Willbert', 'Santa Clara', 6700.00, date '2013-10-10'),(101, 'Tarun', 'Kumar', 'Los Angeles', 7800.00, date '2008-07-17'),(102, 'Savitha', 'Venkat', 'San Dieago', 7100.00, date '2008-04-20'),(103, 'Harish', 'Kumar', 'Glendale', 8100.00,date '2009-11-20'),(104, 'Nancy', 'Grey', 'Fresno', 5600.00, date '2009-09-22'),(105,'Banu', 'Sameera', 'Fremont', 3100.00, date '2015-05-13'), (106, 'Clinton', 'Rozario', 'Dublin', 6700.00, date '2016-09-13'),(107, 'Yamini', 'Shankar', 'Cupertino', 6500.00, date '2018-11-26'),(108, 'Deepika', 'Charan', 'Long Beach', 6170.00, date '2013-10-14'),(109, 'Sam', 'Andreson', 'San Francisco', 4100.00, date '2016-10-25')

Screenshot (5).png

[vapukov]

you have an error in input component, so on this step - it is not important what in insert query

also in your input query, you use sample_empl dataset, and on the picture - src_empl1, so again - we can not be sure both are the same