用c#在sql数据库中插入日期时间值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1032495/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
insert datetime value in sql database with c#
提问by
How do I insert a datetime value into a SQL database table where the type of the column is datetime?
如何将日期时间值插入到列类型为日期时间的 SQL 数据库表中？
回答by atfergs
INSERT INTO <table> (<date_column>) VALUES ('1/1/2010 12:00')
回答by Thorsten Dittmar
The following should work and is my recommendation (parameterized query):
以下应该有效并且是我的建议（参数化查询）：
DateTime dateTimeVariable = //some DateTime value, e.g. DateTime.Now;
SqlCommand cmd = new SqlCommand("INSERT INTO <table> (<column>) VALUES (@value)", connection);
cmd.Parameters.AddWithValue("@value", dateTimeVariable);
cmd.ExecuteNonQuery();
回答by Andrea
It's more standard to use the format yyyy-mm-dd hh:mm:ss(IE: 2009-06-23 19:30:20)
使用格式yyyy-mm-dd hh:mm:ss(IE: 2009-06-23 19:30:20) 更标准
Using that you won't have to worry about the format of the date (MM/DD/YYYY or DD/MM/YYYY). It will work with all of them.
使用它，您不必担心日期的格式（MM/DD/YYYY 或 DD/MM/YYYY）。它将适用于所有人。
回答by Joel
using (SqlConnection conn = new SqlConnection())
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO <table> (<date_column>) VALUES ('2010-01-01 12:00')";
cmd.ExecuteNonQuery();
}
It's been awhile since I wrote this stuff, so this may not be perfect. but the general idea is there.
我已经有一段时间没有写这些东西了，所以这可能并不完美。但总体思路是存在的。
WARNING: this is unsanitized. You should use parameters to avoid injection attacks.
警告：这是未经消毒的。您应该使用参数来避免注入攻击。
EDIT:Since Jon insists.
编辑：因为乔恩坚持。
回答by Reshma
DateTime time = DateTime.Now; // Use current time
string format = "yyyy-MM-dd HH:mm:ss"; // modify the format depending upon input required in the column in database
string insert = @" insert into Table(DateTime Column) values ('" + time.ToString(format) + "')";
and execute the query.
DateTime.Nowis to insert current Datetime..
并执行查询。
DateTime.Now是插入当前日期时间..
回答by Dan
Convert the existing DateTimeobject to a stringwith single quotes around it. I don't think it really matters what format it is, as long as it is valid.
将现有DateTime对象转换为string带有单引号的a 。我认为它是什么格式并不重要，只要它是有效的。
Something like this:
像这样的东西：
string data = "'" + date.ToString() = "'";
Optionally you can create a new format and pass it as a parameter to data.ToString(format);
您可以选择创建一个新格式并将其作为参数传递给data.ToString(format);
回答by PedPak
you can send your DateTime value into SQL as a String with its special format. this format is "yyyy-MM-dd HH:mm:ss"
您可以将 DateTime 值作为具有特殊格式的字符串发送到 SQL 中。这种格式是“yyyy-MM-dd HH:mm:ss”
Example: CurrentTimeis a variable as datetimeType in SQL. And dtis a DateTimevariable in .Net.
示例：CurrentTime是SQL 中datetime类型的变量。和DT是日期时间在.net中的变量。
DateTime dt=DateTime.Now;
string sql = "insert into Users (CurrentTime) values (‘{0}')";
sql = string.Format(sql, dt.ToString("yyyy-MM-dd HH:mm:ss") );
回答by joonas
This is an older question with a proper answer (please use parameterized queries) which I'd like to extend with some timezone discussion. For my current project I was interested in how do the datetimecolumns handle timezones and this question is the one I found.
这是一个较旧的问题，有正确的答案（请使用参数化查询），我想通过一些时区讨论来扩展它。对于我当前的项目，我对datetime列如何处理时区感兴趣，这个问题是我发现的问题。
Turns out, they do not, at all.
事实证明，他们根本没有。
datetimecolumn stores the given DateTimeas is, without any conversion. It does not matter if the given datetime is UTC or local.
datetime列按DateTime原样存储给定值，无需任何转换。给定的日期时间是 UTC 还是本地都没有关系。
You can see for yourself:
你可以亲眼看看：
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM (VALUES (@a, @b, @c)) example(a, b, c);";
var local = DateTime.Now;
var utc = local.ToUniversalTime();
command.Parameters.AddWithValue("@a", utc);
command.Parameters.AddWithValue("@b", local);
command.Parameters.AddWithValue("@c", utc.ToLocalTime());
using (var reader = command.ExecuteReader())
{
reader.Read();
var localRendered = local.ToString("o");
Console.WriteLine($"a = {utc.ToString("o").PadRight(localRendered.Length, ' ')} read = {reader.GetDateTime(0):o}, {reader.GetDateTime(0).Kind}");
Console.WriteLine($"b = {local:o} read = {reader.GetDateTime(1):o}, {reader.GetDateTime(1).Kind}");
Console.WriteLine($"{"".PadRight(localRendered.Length + 4, ' ')} read = {reader.GetDateTime(2):o}, {reader.GetDateTime(2).Kind}");
}
}
}
What this will print will of course depend on your time zone but most importantly the read values will all have Kind = Unspecified. The first and second output line will be different by your timezone offset. Second and third will be the same. Using the "o" format string (roundtrip)will not show any timezone specifiers for the read values.
这将打印什么当然取决于您的时区，但最重要的是读取值都将具有Kind = Unspecified. 第一个和第二个输出行会因您的时区偏移而不同。第二和第三将是相同的。使用“o”格式字符串（往返）不会显示读取值的任何时区说明符。
Example output from GMT+02:00:
GMT+02:00 的示例输出：
a = 2018-11-20T10:17:56.8710881Z read = 2018-11-20T10:17:56.8700000, Unspecified
b = 2018-11-20T12:17:56.8710881+02:00 read = 2018-11-20T12:17:56.8700000, Unspecified
read = 2018-11-20T12:17:56.8700000, Unspecified
Also note of how the data gets truncated (or rounded) to what seems like 10ms.
还要注意数据如何被截断（或四舍五入）到 10 毫秒。
