datetime JDBC date conversion

I am writing a datetime field value to MS SQL Server 7 in the following manner
via a stored procedure:

// item to be written is originally a java.util.Date object
java.util.Date fromDate;
// I'm inserting it here into the database
cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));

// the record in the database appears as follows - as I wanted it to..
12/23/2004 4:30:43 AM

The problem is reading the date FROM the database back into ANY type of Java
Date-related object. No matter what I try, the hour/minutes/seconds are not
returned, and I desperately need the hour and minutes. I don't want to store
the hours and minutes in another field - it just causes more complications.
Does anyone out there know a way to get the ENTIRE date value out of the database?Hi

Are you saying that getTime() returns 00:00:00 ?

Posting your the code that tries to retrive this may help!

John

"billb" <kmilburn@.austin.rr.com> wrote in message
news:3561fddb.0410261050.6c8bafba@.posting.google.c om...
> I am writing a datetime field value to MS SQL Server 7 in the following
manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of
Java
> Date-related object. No matter what I try, the hour/minutes/seconds are
not
> returned, and I desperately need the hour and minutes. I don't want to
store
> the hours and minutes in another field - it just causes more
complications.
> Does anyone out there know a way to get the ENTIRE date value out of the
database?|||
billb wrote:

> I am writing a datetime field value to MS SQL Server 7 in the following manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of Java
> Date-related object. No matter what I try, the hour/minutes/seconds are not
> returned, and I desperately need the hour and minutes. I don't want to store
> the hours and minutes in another field - it just causes more complications.
> Does anyone out there know a way to get the ENTIRE date value out of the database?

Are you sure you tried getTimestamp()?
Joe Weinstein at BEA|||billb wrote:
> I am writing a datetime field value to MS SQL Server 7 in the following manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of Java
> Date-related object. No matter what I try, the hour/minutes/seconds are not
> returned, and I desperately need the hour and minutes. I don't want to store
> the hours and minutes in another field - it just causes more complications.
> Does anyone out there know a way to get the ENTIRE date value out of the database?

Convert DATETIME into CHARATER using MS SQL finction is SELECT statement:

CONVERT ( VARCHAR( length ), some_datetime_colimn, <style> )

where <style> is magic number which defines format( 120 ~ yyyy-mm-dd HH:mi:ss);

get value from ResultSet as string and parse it using

java.text.DateFormat.parseDate(...)|||Hi,

Please use the following to construct a full date object from database date
field.

java.util.Date myDate = new
java.util.Date(myResultSet.getTimestamp("myDate").getTime());

I hope this will help ..

Regards,
Yasir

"billb" <kmilburn@.austin.rr.com> wrote in message
news:3561fddb.0410261050.6c8bafba@.posting.google.c om...
> I am writing a datetime field value to MS SQL Server 7 in the following
manner
> via a stored procedure:
> // item to be written is originally a java.util.Date object
> java.util.Date fromDate;
> // I'm inserting it here into the database
> cstmt.setTimestamp(8, new Timestamp(fromDate.getTime()));
> // the record in the database appears as follows - as I wanted it to..
> 12/23/2004 4:30:43 AM
> The problem is reading the date FROM the database back into ANY type of
Java
> Date-related object. No matter what I try, the hour/minutes/seconds are
not
> returned, and I desperately need the hour and minutes. I don't want to
store
> the hours and minutes in another field - it just causes more
complications.
> Does anyone out there know a way to get the ENTIRE date value out of the
database?

DateTime format during INSERT using MS JDBC driver

Hi,
Using the latest MS JDBC-driver:
The date format of the SQL Server causes me some trouble at the moment:
My INSERT statement uses DateTime values of the format "yyyy-mm-dd
hh:mm:ss.mmm" but they somehow get interpreted as"yyyy-dd-mm" causing a date
format out of range exception.
How can I "force" SQL Server to use my date format? Calling "SET DATEFORMAT
YMD" on each open connection before the statement is executed does not help
but this may be caused by additional connections being opened "behind the
scenes" (so I've been told). I'm not sure this is so even if my connection
pool keeps connections open.
The db-user opening the connection has the correct language setting and the
collation label of the databases is also correct.
So I'm wondering whether it's the regional setting of the Windows account
that runs the SQLServer-service (or SQLSERVERAGENT-Service?) that causes
this? But then - what region would use "yyyy-dd-mm" as it's date format?
Or is there a setting on the JDBC-driver that can modify this behaviour?
- Tim
Hi Tim,
You can either use the parameterized query and pass in a value of a
Java.Sql.Date type. You can thus convert the "yyyy-mm-dd" string to Date.
Below is the code snippet:
PreparedStatement st = connection1.prepareStatement("INSERT
Customers (ArchiveDate) VALUES (?)");
st.setDate(1, Date.valueOf("1999-01-30"));
st.executeUpdate();
Or if you choose to hard code the "yyyy-mm-dd" in the query string, you can
use CONVERT function to convert the string to a datetime sql data type
according to the proper style of date format. 120 is the ODBC canonical
style that converts yyyy-mm-dd hh:mi:ss(24h). Below is the code snippet:
PreparedStatement st = connection1.prepareStatement("INSERT
Customers (ArchiveDate) VALUES (CONVERT (datetime, '1999-01-30', 120) )");
st.executeUpdate();
For more info on CONVERT, please refer to the SQL Server Books Online.
Yilei