Java：插入表日期时间数据 [英] Java: Insert into a table datetime data

问题描述

我试图在MS-SQL数据库中插入一个变量当前的日期和时间。
我使用以下格式：

I am trying to insert into a variable in MS- SQL database the current date and the time. I use this format:

DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); 
Calendar cal = Calendar.getInstance();  
System.out.println(dateFormat.format(cal.getTime()));

我得到了这个结果2013-01-28 09：29：37.941

and I get this as a result 2013-01-28 09:29:37.941

我在数据库中的字段定义为 datetime ，正如我在其他表中看到的那些具有相同字段，日期和时间写得与此完全相同2011-07-05 14：18：33.000。

My field in the database is defined datetime and as I have seen in other tables which have the same field, the date and the time is written exactly like this 2011-07-05 14:18:33.000.

我尝试使用我在java程序中执行的查询插入数据库，但我收到此错误

I try to insert into the database with a query that I do inside a java program, but I get this error

SQL异常：状态：S0003消息：将varchar
数据类型转换为日期时间数据值的类型超出范围。错误
：242

SQL Exception: State : S0003 Message: The conversion of a varchar data type to a datetime data type of the value is out of range. Error : 242

我的查询是这样的：

query = "INSERT INTO Companies CreatedOn"+ 
         "VALUES ('" + dateFormat.format(cal.getTime()) + "')"

但我不明白我做错了什么。

but I don't understand what I am doing wrong.

推荐答案

根据错误说明，您正在向数据库中插入不正确的类型。请参阅 JDBC to MSSQL 。您应该将日历转换为时间戳。

According to the error description, you are inserting an incorrect type into the database. See JDBC to MSSQL. You should convert Calendar to Timestamp.

尝试使用：

PrepareStatement statement 
    = connection.prepareStatement("INSERT INTO Companies CreatedOn VALUES(?)");
java.sql.Timestamp timestamp = new java.sql.Timestamp(cal.getTimeInMillis());
statement.setTimestamp(1, timstamp);
int insertedRecordsCount = statement.executeUpdate();

这篇关于Java：插入表日期时间数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！