如何在SQL Server 2005中存储日期和时间? [英] How to store date and time in SQL Server 2005?
问题描述
我正在使用C#和SQL Server2005。我想在表中存储日期和时间。在SQL Server中,我使用 smalldatetime
数据类型。我正在使用此代码。
I am using C# and SQL Server 2005. I want to store date and time in my table. In SQL Server, I use smalldatetime
datatype. I am using this code.
TimeZoneInfo ob1 = TimeZoneInfo.FindSystemTimeZoneById("India Standard Time");
DateTime dt = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, ob1);
并通过此查询插入数据
insert into tablename (name,sub_date) values (@name,'{0}')",dt.ToShortTimeString()
但是,问题是我的表中的日期和时间像这样存储: 1/1/1900 5:15:00 PM
。不存储实际日期。如何在数据库中存储实际日期和时间。
but, the problem is that in my table date and time is stored like this 1/1/1900 5:15:00 PM
. Actual date is not stored. How to store actual date and time in database.
注意:
推荐答案
尝试类似的事情,因为日期和时间对我来说都很重要。 :
Try something like this:
// Define insert statement with parameters
string insertStmt = "INSERT INTO dbo.tablename (name, sub_date) VALUES (@name, @mydate)";
// setup connection and command objects
using(SqlConnection conn = new SqlConnection("your-connection-string-here"))
using(SqlCommand cmd = new SqlCommand(insertStmt, conn))
{
// add parameters to command
cmd.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = ".....";
cmd.Parameters.Add("@mydate", SqlDbType.DateTime).Value = dt;
// open connection, execute query, close connection
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
这应该可以解决问题!从 DateTime
到字符串的任何混乱转换- DateTime
的值被原样保存并以相同的格式存储在SQL Server。通过使用参数,您既消除了SQL注入攻击的可能性,而且,如果您需要使用不同的值多次调用该查询,则可以重新使用该查询,从而提高性能!
That should do the trick! No messy conversion from DateTime
to string - the DateTime
value is preserved "as is" and stored in that same format in SQL Server. By using parameters, you've both eliminated the possibility of a SQL injection attack, and also, this query can be re-used if you need to call it multiple times with different values, thus increasing performance, too!
这篇关于如何在SQL Server 2005中存储日期和时间?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!