One Star

error Timestamp format must be yyyy-mm-dd hh:mm:ss on date field

Hi,
i am using a generic ODBC (tDBinput) to read from hyperfile sql tables, when i read date and hour fields as strings they have this format yyyyMMdd and HH:mm:ss, but when i set fields to the Date type and lunch the job i get this error
Exception in component tDBInput_1
java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss
at java.sql.Timestamp.valueOf(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getTimestamp(Unknown Source)
at exportodbc.sdsqqq_0_1.sdsqqq.tDBInput_1Process(sdsqqq.java:3375)
at exportodbc.sdsqqq_0_1.sdsqqq.runJobInTOS(sdsqqq.java:4895)
at exportodbc.sdsqqq_0_1.sdsqqq.main(sdsqqq.java:4815)

dont mind the job name, its for testing purposes Smiley Happy
i even tried using the personalised date pattern in schema definition, i still get the error.
for my job to work i read the dates and hours as strings and then convert them at the output using tmap and the function parseDate, but it hard since i have to type the function for every date fields and there is so many of them.
2 REPLIES
Seventeen Stars

Re: error Timestamp format must be yyyy-mm-dd hh:mm:ss on date field

I would read at first this field as text and parse the text content later in the job to a timestamp. In this way you learn in which format your date will be retrieved. It looks like, the actual data type of this field is also a text based format. The Timestamp.valueOf method ill only be invoked of the given object is in fact not a timestamp.
One Star

Re: error Timestamp format must be yyyy-mm-dd hh:mm:ss on date field

Thank you Jan, the problem as i see it is that the componement tDBinput that seems to default to timestamp when a field type is set to Date; even after adding a personalized Date Pattern to the schema definition ( i know its "yyyyMMdd" ) it wouldn't take it into consideration ! i have to manuelly use the TalendDate routine parseDate on the tMap output row for each and every Date field to convert it from string wich i find very annoying since there are many Date fields on the tables i am querying.
is there any way we can change that behavior ?
PS : i am using TOS 3.0.4 (its for compatibility with the talend engine in spagobi 3.6 wich is also 3.0.4)