mssql 2005 datetime和jdbc [英] mssql 2005 datetime and jdbc

问题描述

如何在带有JDBC的MS SQL 2005中插入日期时间?我使用存储过程插入数据库:

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 

我的JDBC代码是:

    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();

但这不是在数据库中插入小时，分钟，秒，毫秒吗?如何从JDBC插入此信息?提前致谢.我发现很难理解日期和时间的东西..到处都是这么多的变化..

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..

编辑

表结构为:

编辑

方法cvtToGmt()来自如何转换格林尼治标准时间的本地日期

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

推荐答案

EDIT2

使用callableStatement.setTimestamp()-这将被视为时间戳. javax.sql.Timestamp扩展了java.util.Date，因此您无需进行转换.

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

上一个

也许cvtToGmt()函数可以削减分数.

Maybe the cvtToGmt() function cuts the fractions.

可能您的表中的列类型错误，不能仅存储Date.

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

Date列类型:YYYY-MM-DD http://msdn.microsoft .com/en-ie/library/bb630352.aspx

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

这篇关于mssql 2005 datetime和jdbc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！