asked on

Cannot convert value to TIMESTAMP.

I have a problem with trying to pull a result from a query. I get the error of

Cannot convert value '00000000000000' from column 14 to TIMESTAMP.

Timestamp date = r.getTimestamp("sub_DtTmIn");
String strdate = date.toString();

Using a MySQL database.

The column sub_DtTmIn is a timestamp(14) but when I looked into the data in other rows the timestamp is not in seconds but is in the form of YYYYMMDDHHMMSS. I know I can get this to work in PHP but PHP doesn't care what values are returned since it only has vars and not datatypes.

Thanks

petmagdy

not sure but try this:

Timestamp date = new TimeStamp(r.getDate("sub_DtTmIn").getTime());

CEHJ

Can you post what this prints?

System.out.println(r.getResultSetMetaData().getColumnTypeName(x)); // (where 'x' is the i-based index of column sub_DtTmIn

wgordy

ASKER

System.out.println(r.getResultSetMetaData().getColumnTypeName(x));

outputs - TIMESTAMP

The timestamp on another row that's not 0000000000000 is 20050203124323

CEHJ

Which driver are you using?

CEHJ

See "TIMESTAMP[(M)] "

at

http://dev.mysql.com/doc/mysql/en/date-and-time-type-overview.html

I haven't quite taken all that in, but i suggest these values may have been inserted as Strings, so i'd do

String sDate = r.getString("sub_DtTmIn");

and then turn it into java.sql.Timestamp if that's what you want, or indeed java.util.Date

CEHJ

try {
      SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
      Date date = sdf.parse(r.getString("sub_DtTmIn"));
}
catch(ParseException e) {
      e.printStackTrace();
}

Mick Barry

clean up the data in your database :)

ASKER CERTIFIED SOLUTION

Mick Barry

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wgordy

ASKER

I didn't do the database, I'm just trying to do something stuff in jsp but I keep running into stupid little problems! = P. I'll try that.

wgordy

ASKER

So where should I go from there? Should I just catch each exception for any timestamp I use and put 0's in for that variable when it's caught? Or is there a nice way to handle it?

CEHJ

String sDate = r.getString("sub_DtTmIn");
if (Long.parseLong(sDate) > 0) {
// now do it as i mentioned
}

Mick Barry

The code I posted above will handle it, thats the nicest way I can think of to handle it.

CEHJ

The inserts have probably been made as Strings, therefore you should probably handle them as Strings. It may be that somebody has seen fit to insert "00000000000000" instead of "" which is a possible explanation for what you see

Mick Barry

Thats irrelevant, the column is a Timestamp column and should be parsed by the driver.

CEHJ

>>Thats irrelevant

By no means - you obviously haven't read the link i posted ;-)

wgordy

ASKER

It still fails when asking for 00000000000000. Object's code is the only one that works so far. Thanks everyone!

CEHJ

Catching an exception is obviously a workaround - the thing to do is find a solution ;-)

Mick Barry

> Object's code is the only one that works so far.

Good stuff :)

Mick Barry

> Catching an exception is obviously a workaround - the thing to do is find a solution ;-)

That is the solution :D

CEHJ

Have you got a driver that's not as current as your db?

CEHJ

You may well find that:

update yourtable set sub_DtTmIn='' where sub_DtTmIn='00000000000000'

will fix it

Mick Barry

> update yourtable set sub_DtTmIn='' where sub_DtTmIn='00000000000000'

already suggested, it is not an option.

Mick Barry

> Object's code is the only one that works so far.

Does it report a warning btw?

CEHJ

>>It still fails when asking for 00000000000000.

Does it fail if you call getLong on it too?

Mick Barry

Theres no need to call getLong()

CEHJ

LOL