从 C# 中的 SQL 数据库读取 DateTime 值时没有毫秒值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1460997/
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
No milliseconds value when reading DateTime values from a SQL database in C#
提问by Justin
I have high precision dates stored in an SQL server, e.g.
我在 SQL 服务器中存储了高精度日期，例如
2009-09-15 19:43:43.910
However when I convert that value into a DateTime the miliseconds value of the resulting DateTime value is 0:
但是，当我将该值转换为 DateTime 时，生成的 DateTime 值的毫秒值为 0：
reader["Timestamp"] = 15/09/2009 19:43:43.000
Having these DateTime values in precision down to milliseconds is very important to me - what is the best way of doing this?
将这些 DateTime 值精确到毫秒对我来说非常重要 - 这样做的最佳方法是什么？
UPDATE:This is the code that performs the conversion:
更新：这是执行转换的代码：
DateTime myDate = (DateTime)reader[Timestamp"];
There is nothing special about the SELECTstatement, in fact it is a SELECT *- no fancy casts or anything
该SELECT语句没有什么特别之处，实际上它是一个SELECT *- 没有花哨的演员表或任何东西
It appears that the DateTime object returned by the SqlDataReadersimply is not populated with the Millisecond value
似乎SqlDataReader简单地返回的 DateTime 对象没有填充毫秒值
采纳答案by Justin Grant
Here's how I'd try to troubleshoot this:
这是我尝试解决此问题的方法：
step through in the debugger and look at the type and value of reader[Timestamp"]. If the type is not SqlDateTime, that would make me suspicious-- I'd then look at the query to figure out why that column was returning another type instead of DATETIME (or DATETIME2, etc.)
if that value is SqlDateTime and it does contains milliseconds, then I'd look at the cast as the source of the problem. To validate this, I'd try (in debugger or code) SqlDataReader.GetDateTime() and SqlDataReader.GetSqlDateTime() to see if either returns the correct result. This admittedly seems really unlikely as source of the problem-- casting should work fine.
if that value from #1 is SqlDateTime but contains no milliseconds, then I'd look to an upstream problem in the database-- in other words your query is returning something without milliseconds. when you execute the exact same query in Management Studio, do you see milliseconds?
在调试器中逐步执行并查看 reader[Timestamp"] 的类型和值。如果类型不是 SqlDateTime，那会让我产生怀疑——然后我会查看查询以找出该列返回另一个的原因键入而不是 DATETIME（或 DATETIME2 等）
如果该值是 SqlDateTime 并且它确实包含毫秒，那么我会将强制转换视为问题的根源。为了验证这一点，我会尝试（在调试器或代码中） SqlDataReader.GetDateTime() 和 SqlDataReader.GetSqlDateTime() 以查看是否返回正确的结果。诚然，这似乎不太可能是问题的根源——铸造应该可以正常工作。
如果#1 中的值是 SqlDateTime 但不包含毫秒，那么我会查看数据库中的上游问题——换句话说，您的查询返回的内容没有毫秒。当您在 Management Studio 中执行完全相同的查询时，您是否看到毫秒？
My guess is this is a query-related issue. But am intrigued to find our more.
我的猜测是这是一个与查询相关的问题。但我很想找到我们的更多。
回答by Lukasz Lysik
Maybe this (Difference between DateTime in c# and DateTime in SQL server)will help a little.
回答by Magnus Johansson
That is because the default format string for DateTime doesn't include milliseconds.
这是因为 DateTime 的默认格式字符串不包括毫秒。
If you use a custom format, you will see the milliseconds value.
如果您使用自定义格式，您将看到毫秒值。
An example:
一个例子：
public class Program
{
private static string connString = @"Data Source=(local);Initial Catalog=DBTest;Integrated Security=SSPI;";
public static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT * FROM MilliSeconds"))
{
cmd.Connection = conn;
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
DateTime dt = (DateTime)reader["TimeCollected"];
int milliSeconds = dt.Millisecond;
Console.WriteLine(dt.ToString("yyyy-MM-dd HH:mm:ss.fff"));
}
}
}
Console.ReadLine();
}
}
From a database with these values:
从具有这些值的数据库：
1 2009-09-22 18:11:12.057
2 2009-09-22 18:11:28.587
3 2009-09-22 18:11:29.820
The resulting output from the code above is:
上面代码的结果输出是：
2009-09-22 18:11:12.057
2009-09-22 18:11:28.587
2009-09-22 18:11:29.820
回答by CyberMacgyver
I had this same problem and after some reading it turns out that when you retrieve the date as you were doing
我遇到了同样的问题，经过一些阅读后发现，当您像往常一样检索日期时
DateTime myDate = (DateTime)reader["Timestamp"];
the SQLDataReader drops the milliseconds. However if you use the GetDateTime method of the SQLDataReader it returns a DateTime object which preserves the milliseconds:
SQLDataReader 删除毫秒。但是，如果您使用 SQLDataReader 的 GetDateTime 方法，它会返回一个保留毫秒的 DateTime 对象：
reader.GetDateTime(reader.GetOrdinal("Timestamp"));
回答by Gabriele Giuseppini
I had the same problem, and I solved it by persisting the C# DateTime as a SQL bigintpopulated with DateTime.Ticks. This preserves the full DateTime precision. And of course can be de-serialized with the DateTime(long ticks)constructor.
我遇到了同样的问题，我通过将 C# DateTime 作为填充了DateTime.Ticks的 SQL bigint来解决它。这将保留完整的 DateTime 精度。当然，可以使用DateTime(long ticks)构造函数反序列化。
