blocks|key|1339876|text|有没有可能日期实际上在这个范围之外？它来自用户输入吗？如果这两个问题中的任何一个的答案是肯定的，那么你应该始终检查-否则你的应用程序很容易出错。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1339877|您可以很容易地将日期格式化以包含在SQL语句中：|1339878|var+sqlFormattedDate+=+myDateTime.Date.ToString("yyyy-MM-dd+HH:mm:ss");|code-block|syntax|javascript|1339879|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Is it possible that the date could actually be outside that range? Does it come from user input? If the answer to either of these questions is yes, then you should always check - otherwise you're leaving your application prone to error.

You can format your date for inclusion in an SQL statement rather easily:

<pre><code>var sqlFormattedDate = myDateTime.Date.ToString("yyyy-MM-dd HH:mm:ss");
</code></pre>

blocks|key|1124886|text|如果您正在检查DBNULL，那么将SQL+Datetime转换为.NET+DateTime应该不是问题。但是，在将.NET+DateTime转换为有效的DateTime时可能会遇到问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1124887|SQL+Server不识别早于1753年1月1日的日期。那一年，英国采用了公历。通常，检查DateTime.MinValue就足够了，但如果您怀疑数据可能早于18世纪，则需要进行另一次检查或使用不同的数据类型。(我经常想知道博物馆在他们的数据库中使用什么)|1124888|没有必要检查最大日期，SQL+Server和.NET+DateTime的最大日期都是12/31/9999。这可能是一个有效的业务规则，但不会造成问题。|1124889|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

If you are checking for DBNULL, converting a SQL Datetime to a .NET DateTime should not be a problem. However, you can run into problems converting a .NET DateTime to a valid SQL DateTime. 

SQL Server does not recognize dates prior to 1/1/1753. Thats the year England adopted the Gregorian Calendar. Usually checking for DateTime.MinValue is sufficient, but if you suspect that the data could have years before the 18th century, you need to make another check or use a different data type. (I often wonder what Museums use in their databases)

Checking for max date is not really necessary, SQL Server and .NET DateTime both have a max date of 12/31/9999 It may be a valid business rule but it won't cause a problem.

blocks|key|1339992|text|另外，请记住，时间的分辨率是不同的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1339993|http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.aspx|offset|length|1339994|SQL+one为3.33ms，.net+one为100+ns。|1339995|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|25|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

Also please remember resolutions [quantum of time] are different.

<a href="http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.aspx" rel="nofollow noreferrer">http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.aspx</a>

SQL one is 3.33 ms and .net one is 100 ns.

blocks|key|1125046|text|在我用entitie做这件事的过程中，我跌跌撞撞地来到这里，只是回过头来发布我发现的……|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1125047|在使用EF4时，可以使用BitConverter从.NET的datetime填充"a+sql's“DateTime列。|1125048|EntitieObj.thetime+=+BitConverter.GetBytes(DateTime.Now.ToBinary());|code-block|syntax|javascript|1125049|Fakrudeen的链接也给我带来了更多...谢谢。|1125050|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

on my quest to do this with entitie, i stumbled over here, just hitting back to post what i've found out...

when using EF4, "a sql's" datetime column can be filled from .NET's DateTime using BitConverter. 

<pre><code>EntitieObj.thetime = BitConverter.GetBytes(DateTime.Now.ToBinary());
</code></pre>

also Fakrudeen's link brought me further... thank you.

blocks|key|1125102|text|-To仅比较日期部分，您可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1125103|var+result+=+db.query($"SELECT+*+FROM+table+WHERE+date+>=+'{fromDate.ToString("yyyy-MM-dd")}'+and+date+<=+'{toDate.ToString("yyyy-MM-dd"}'");|code-block|syntax|javascript|1125104|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

-To compare only the date part, you can do:

<pre><code>var result = db.query($"SELECT * FROM table WHERE date &gt;= '{fromDate.ToString("yyyy-MM-dd")}' and date &lt;= '{toDate.ToString("yyyy-MM-dd"}'");
</code></pre>

blocks|key|1340162|text|var+sqlCommand+=+new+SqlCommand("SELECT+*+FROM+mytable+WHERE+start_time+>=+@StartTime");
sqlCommand.Parameters.Add("@StartTime",+SqlDbType.DateTime);
sqlCommand.Parameters("@StartTime").Value+=+MyDateObj;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1340163|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>var sqlCommand = new SqlCommand("SELECT * FROM mytable WHERE start_time &gt;= @StartTime");
sqlCommand.Parameters.Add("@StartTime", SqlDbType.DateTime);
sqlCommand.Parameters("@StartTime").Value = MyDateObj;
</code></pre>

While converting .NET DateTime (when is default(DateTime)) to SqlDateTime should I always check if the .NET date is between SqlDateTime.MinValue and SqlDateTime.MaxValue [or] Is there a good way to do this.

.NET DateTime to SqlDateTime Conversion

SQL Server

数据库

在将.NET DateTime (默认(DateTime))转换为SqlDateTime时，我是否应该始终检查.NET日期是否在SqlDateTime.MinValue和SqlDateTime.MaxValue之间，或者有没有好的方法这样做。

问.NET DateTime到SqlDateTime的转换
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问.NET DateTime到SqlDateTime的转换EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问.NET DateTime到SqlDateTime的转换
EN