java date obj to sql date obj

i want to put java date(actually time from 1 jan, 1970) into sql database(sql
6.5 server). what should be type of that field in sql table? how to store
java obj into it? does sql6.5 server support BIGINT that is given in JDBC
book? do i have to use a driver other that jdbcdbc?

-manisha

[Paul Clapham]

Do you want to have the database field contain a date or the "time from 1
jan, 1970"? These may be the same in Java but they aren't the same in the
database. I'd suggest using either a date or a timestamp database field
("date" is the date with no time, "timestamp" combines date and time).

You store java objects into database fields using methods from JDBC.
Personally I use PreparedStatement objects whenever possible, then I don't
have to tear my hair out over how the database wants me to format dates, I
just write "pstmt.setDate(3, new java.sql.Date(theDate.getTime())" to store
my java Date object called "theDate".

Don't know about SQL server. It probably comes with documentation.

You can use any driver that will support the database you want to use.

PC2

<mhadke@yahoo.com> wrote in message news:3a9671f7$1@news.devx.com...
>
> i want to put java date(actually time from 1 jan, 1970) into sql
database(sql
> 6.5 server). what should be type of that field in sql table? how to store
> java obj into it? does sql6.5 server support BIGINT that is given in JDBC
> book? do i have to use a driver other that jdbcdbc?
>
> -manisha

[Fabio Luis De Paoli]

mhadke@yahoo.com wrote:

> i want to put java date(actually time from 1 jan, 1970) into sql database(sql
> 6.5 server). what should be type of that field in sql table? how to store
> java obj into it? does sql6.5 server support BIGINT that is given in JDBC
> book? do i have to use a driver other that jdbcdbc?
>
> -manisha

You should use some type of "timestamp" column, and store the java.util.Date as a
java.sql.Date or java.sql.Timestamp object.

However WATCH OUT for:
- buggy jdbcs that incorrectly add one or two miliseconds to your Date during the
insert or the read operation
- buggy jdbcs that use the default Timezone to translate your Date's value to
GMT+0 when storing data, but not when reading it (ARGH... happened to me)

I grew tired of playing with databases and JDBCs and decided to wrap the Date
into a String, using the class below. Feel free to use it.

import java.util.Date;

/**
* This class is used to avoid date manipulation errors found in some Relational
* Database Management Systems or their JDBC drivers. These limitations may
* cause the date to be stored slightly different than it exists in the JVM,
* sometimes with a small milisecond difference.<br>
* <br>
* The use of this class ensures that java.util.Date objects are able to be
stored
* in their natural format (a long number) in any RDBMS that supports String
* columns (all of them).<br>
*/
public class DateSQLStringWrapper
{
private static final String zeroes = "0000000000000000000";

public Date toDate(String aStr) {
try {
long tempLong;
tempLong = (Long.valueOf(aStr.substring(1,20))).longValue();
if (aStr.substring(0,1).equals("N")) tempLong = tempLong * (-1L);
return new Date(tempLong);
} catch (NumberFormatException nfe) {
nfe.printStackTrace();
throw new InternalError(nfe.getMessage());
}
}

/**
* This method will transform a Date into a 20 character long String
* in the format:<br>
* <br>
* SNNNNNNNNNNNNNNNNNNN<br>
* <br>
* Where:<br>
* <ul><li>S will contain P for positive, N for negative. This ensures
correct
* sorting of dates, because N comes before P. Zero is considered positive.
* <li>NNNNNNNNNNNNNNNNNNN will contain zero padded, nineteen digit, string
* translation of the Date's internal long number.
* </ul>
* For example, the Date "2001-Feb-19 0:00:00.000 GMT-3" will become the
String
* "P0000000982591200000".
* @author Fabio Luis De Paoli
* @since 19-Jan-2001
*/
public String toStr(Date aDate) {
String tempOutput;
String tempSignal;
long tempLong = aDate.getTime();
if (tempLong < 0) {
tempLong = tempLong * (-1);
tempSignal = "N";
} else {
tempSignal = "P";
}
tempOutput = zeroes + tempLong;
int beginIndex = tempOutput.length() - 19;
return (tempSignal + tempOutput.substring(beginIndex, beginIndex + 19));
}
}