Question Re: Converitng SQL Anywhere's datetime value into java.Util.Date in Technology Q&A

Converitng SQL Anywhere's datetime value into java.Util.Date

Former Member — Mon, 25 Nov 2013 05:27:04 GMT

Hi,

I am exporting the data of any table in which there is field like CREATED_ON whoese datatype is date time.

While exporting value of the field is 2013-11-18 15:37:47.123 When I am going to convert the above value into java.Util.Date. I am writing following

DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date;
date = formatter.parse(columnValue);
System.out.println("yyyy-MM-dd HH:mm:ss "+date);

Error is coming due to "15:37:47.123" format malformed. Because extra value ".123" after HH:mm:ss is giving error. When I remove the extra ".123" error is gone.

So any one tell what is this extra value and how can I parse it in same format by giving correct date format.

Re: Converitng SQL Anywhere's datetime value into java.Util.Date

Former Member — Mon, 25 Nov 2013 08:51:39 GMT

Which driver are you using to fetch the data from the server and what data type are you fetching the datetime value as? In other words, are you using jConnect or the SQL Anywhere JDBC Driver and are you fetching the datetime value using getString(), getTimestamp(), ...? If you are using the SQL Anywhere JDBC Driver and you fetch using getTimestamp() then you should be able to convert the java.sql.Timestamp value to a java.util.Date value without issue. If you are fetching as string and have to continue doing so, then you might want to consider temporarily setting the timestamp_format option to not include the fractional seconds when fetching the datetime value. See the documentation on timestamp_format database option. Make sure you use SET TEMPORARY OPTION to when changing the timestamp_format so that you do not mess up other connections.

Re: Converitng SQL Anywhere's datetime value into java.Util.Date

Former Member — Wed, 27 Nov 2013 04:14:30 GMT

I am using jconn4 driver to connect the SQL Anywhere and the datatype is datetime. I am making connection using mybatis api so whenever it fetch the data it treat it as util.Date by default and as it is write in csv so when i will read it, it is a string and I want to convert it into util.Date

By the way I got solution for the same.

Re: Converitng SQL Anywhere's datetime value into java.Util.Date

VolkerBarth — Wed, 27 Nov 2013 05:27:52 GMT

By the way I got solution for the same.

Feel free to tell more about that solution, in case you expect others could learn from that, too:)

Re: Converitng SQL Anywhere's datetime value into java.Util.Date

Former Member — Sun, 01 Dec 2013 17:27:44 GMT

I've had the same problem, and it's even more complex because you forgot that a time zone might also be involved.

Here is my potential solution, in another answered question: JDBC Retrieving timestamp with Time Zone

Re: Converitng SQL Anywhere's datetime value into java.Util.Date

Former Member — Thu, 05 Dec 2013 07:24:01 GMT

This is solution what I got...

String columnValue="2013-11-18 15:37:47.000";
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
Date date;
date = formatter.parse(columnValue);