CODEWITHSUNDEEP
javasql-server-2005datetimejdbc
lol
lol

Reputation: 3390

mssql 2005 datetime and jdbc

How can I insert datetime in MS SQL 2005 with JDBC? I using stored procedures to insert in DB:

ALTER proc [dbo].[sp_insertid_report]
@stream_time_gmt as datetime,
@stream_time_local as datetime,
@start_time_gmt as datetime,
@end_time_gmt as datetime,
@start_time_local as datetime,
@end_time_local as datetime,
@note_id as int,
@reported_by as varchar(100),
@date_reported as datetime,
@date_created as datetime,
@date_updated as datetime,
@stream_id as int,
@Fp_file_path as varchar(300),
@Fp_name as varchar(200), 
@Is_Allowed as varchar(2)

as

begin  
insert into id_reports(stream_time_gmt,stream_time_local,start_time_gmt,end_time_gmt,start_time_local,end_time_local,
note_id,reported_by,date_reported,date_created,date_updated,stream_id,Fp_file_path,Fp_name,Is_Allowed)
values(@stream_time_gmt,@stream_time_local,@start_time_gmt,@end_time_gmt,@start_time_local,
@end_time_local,@note_id,@reported_by,@date_reported,@date_created,@date_updated,@stream_id,@Fp_file_path,@Fp_name,@Is_Allowed)
end

And my JDBC code is:

    callableStatement = connection.prepareCall("{ call dbo.sp_insertid_report(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");
        callableStatement.setDate(1, new Date(cvtToGmt(new java.util.Date(modifiedTime.toMillis())).getTime()));
        callableStatement.setDate(2, new Date(modifiedTime.toMillis()));
        callableStatement.setDate(3, new Date(cvtToGmt(startTime).getTime()));
        callableStatement.setDate(4, new Date(cvtToGmt(endTime).getTime()));
        callableStatement.setDate(5, new Date(endTime.getTime()));
        callableStatement.setDate(6, new Date(endTime.getTime()));
        callableStatement.setInt(7, songID);
        callableStatement.setString(8, AudioMatcherService.hostName);
        java.util.Date date = new java.util.Date();
        callableStatement.setDate(9, new Date(date.getTime()));
        callableStatement.setDate(10, new Date(date.getTime()));
        callableStatement.setDate(11, new Date(date.getTime()));
        callableStatement.setInt(12, channel.getAssignmentID());
        callableStatement.setString(13, "no");
        callableStatement.setString(14, "no");
        callableStatement.setString(15, "Y");
        callableStatement.execute();

But this is not inserting hour, minute, second, milliseconds in DB? How can I insert this information from JDBC? Thanks in advance. I find it very hard to understand date and time things.. so many variations everywhere..

EDIT

Table structure is:

enter image description here

EDIT

Method cvtToGmt() is from How to convert a local date to GMT

Upvotes: 2

Views: 3244

Answers (2)

gaborsch
gaborsch

Reputation: 15748

EDIT2

Use callableStatement.setTimestamp() - this will treat as a Timestamp. javax.sql.Timestamp extends java.util.Date so you don't need to convert.

Previous

Maybe the cvtToGmt() function cuts the fractions.

Probably you have the wrong column type in your table, that does not store only the Date.

Date column type: YYYY-MM-DD http://msdn.microsoft.com/en-ie/library/bb630352.aspx

Datetime column type: YYYY-MM-DD hh.mm.ss.nnn http://msdn.microsoft.com/en-ie/library/ms187819.aspx

Upvotes: 2

user330315
user330315

Reputation:

You need to use java.sql.Timestamp not java.sql.Date

java.util.Date is only for "real" date columns and will remove the time part. If you need to store date and time you have to use java.sql.Timestamp.

From the Javadocs:

To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.

And of course you will need to use setTimestamp() instead of setDate() as well.

Upvotes: 4

Related Questions