C# SQL Server 中的日期时间问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19018600/
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
DateTime issue in SQL Server
提问by NeoVe
I'm trying to do an INSERT into an already set DB in MS SQL Server, the DB server is in a shared hosting (godaddy).
我正在尝试对 MS SQL Server 中已设置的数据库执行 INSERT，该数据库服务器位于共享主机（godaddy）中。
And what i'm trying to achieve is to store an article into the database, but everytime i publish or get a preview of the article the server throws me
我想要实现的是将一篇文章存储到数据库中，但是每次我发布或预览文章时，服务器都会抛出我
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.]
System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam) +5635
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1379
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +53
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +134
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41
System.Data.Common.DbCommand.ExecuteReader() +12
System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) +1306
System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) +118
System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) +342
System.Data.Linq.StandardChangeDirector.DynamicInsert(TrackedObject item) +145
System.Data.Linq.StandardChangeDirector.Insert(TrackedObject item) +215
System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) +378
System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +372
System.Data.Linq.DataContext.SubmitChanges() +23
sgc.Servicio.CrearServicio.InsertDB(Object sender, EventArgs e) in CrearArticulo.aspx.cs:114
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9553178
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +9642898
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724
I have a Datetimetype field in the shared hosting which should take the date of the article, btw it doesn't throws me this error on other article tables. Just on this one, maybe a typo or something?
我Datetime在共享主机中有一个类型字段，它应该采用文章的日期，顺便说一句，它不会在其他文章表上引发此错误。就在这一点上，也许是打字错误之类的？
This is my code (The code which throws the error):
这是我的代码（引发错误的代码）：
public partial class CrearServicio : System.Web.UI.Page
{
string tpCont = "1";
int estatus = 1;
bool withResponse = true;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
TextBox1.Attributes.Add("onKeyDown", "textCounter(this, 'counter1', 500)");
TextBox1.Attributes.Add("onKeyUp", "textCounter(this, 'counter1', 500)");
datetime.Text = DateTime.Now.ToString("yyyyMMddHHmmss");
if (Request.QueryString["tpCont"] != null)
{
tpCont = Request.QueryString["tpCont"];
volver.HRef = "elegir.aspx?tpCont=" + tpCont;
volver1.HRef = "elegir.aspx?tpCont=" + tpCont;
}
ConexionServiciosDataContext db = new ConexionServiciosDataContext();
tpContinpt.Text = tpCont;
int role = kernel.getUserRole(User.Identity.Name);
int tipoUsuario = role;
if (tipoUsuario == 1 || tipoUsuario == 2)
{
mainButton.Visible = true;
saveButton.Visible = true;
displayButton.Visible = true;
}
}
}
protected void SaveDBAndDisplay(object sender, EventArgs e)
{
withResponse = false;
SaveDB(sender, e);
}
protected void SaveDB(object sender, EventArgs e)
{
estatus = 2;
InsertDB(sender, e);
}
protected void InsertDB(object sender, EventArgs e)
{
tpCont = tpContinpt.Text;
if (TextArea1.Text.Length > 0)
{
ConexionServiciosDataContext db = new ConexionServiciosDataContext();
Servicios columna = new Servicios();
columna.Título = TextBox1.Text.ToString();
string line = TransformarString(TextArea1.Text.ToString());
columna.Contenido = line.ToString();
string[] IDsTitlesLocations = { };
db.Servicios.InsertOnSubmit(columna);
db.SubmitChanges();
}
}
}
Excuse me if i write too much but i'm just stuck on this, maybe i should set the datetime format on the server?
对不起，如果我写得太多但我只是坚持这个，也许我应该在服务器上设置日期时间格式？
Anyone could shed some light on this?
任何人都可以对此有所了解吗？
采纳答案by Khan
I would assume that Servicioshas DateTimeproperty which is not being set.
我会假设它Servicios具有DateTime未设置的属性。
DateTimes are not nullable, and when they're not set, they default to {1/1/0001 12:00:00 AM}.
DateTimes 不可为空，如果未设置，则默认为{1/1/0001 12:00:00 AM}.
Upon calling db.SubmitChanges(), you're attempting to insert a value into a DateTimecolumn which is a lower value that SQL SERVER supports as seen in your exception.
在调用 时db.SubmitChanges()，您试图将一个值插入到一个DateTime列中，该值是 SQL SERVER 支持的较低值，如您的异常中所见。
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
SqlDateTime 溢出。必须在 1/1/1753 12:00:00 AM 和 12/31/9999 11:59:59 PM 之间。
回答by siddhesh
According to me,it would be as follow:
在我看来，这将如下所示：
datetime.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
回答by siddhesh
try DateTime.Nowand if its case of textfeild DateTime.Now.ToString();hope this work.
尝试DateTime.Now，如果它的情况下textfeild DateTime.Now.ToString();希望这项工作。
It works fine in almost same case i have.
它在我遇到的几乎相同的情况下工作正常。
1/1/0001 12:00:00 AM etc are defaults of sqlServer and in above comment if after deleting the method its doing nothing means that its not updating the reference or something like this.
1/1/0001 12:00:00 AM 等是 sqlServer 的默认值，在上面的评论中，如果删除该方法后它什么都不做意味着它没有更新引用或类似的东西。
Make sure what you want to delete is deleted and if its not then enforce it by changing the properties and refresh + check
确保您要删除的内容已删除，如果没有，则通过更改属性并刷新 + 检查来强制执行它
回答by Vikash Singh
I cant understand why you are converting a datetime into String ??
我无法理解您为什么要将日期时间转换为 String ？
datetime.Text = DateTime.Now.ToString("yyyyMMddHHmmss");
if you have to insert datetime in your DB then just pass that datetime variable to insert command.
如果您必须在数据库中插入日期时间，则只需将该日期时间变量传递给插入命令。
DateTime dt = new DateTime();
dt = DateTime.Now.Date;
When you will insert it to DB, DB will convert it to its appropriate datetime format. Just try it.
当您将其插入 DB 时，DB 会将其转换为合适的日期时间格式。就试一试吧。
回答by Jens H
Be aware that MS SQL Server's DateTimeand the C# DateTimehave differentMinValues.
请注意 MS SQL ServerDateTime和 C#DateTime具有不同的MinValues。
While MS SQL DateTime begins only at {1/1/1753 12:00:00 AM}the C# DateTime start with {1/1/0001 12:00:00 AM}.
虽然 MS SQL DateTime 仅从{1/1/1753 12:00:00 AM}C# DateTime 开始，但以{1/1/0001 12:00:00 AM}.
There are 3 approaches that come to my mind:
我想到了3种方法：
Make sure in your code that the object that you want to save has a valid DateTime value set before saving it. The other posters already made some suggestions for this.
Add a default value to the column on the database side. To me, this seems to be the easiest and cleanest approach. You would neither need to change the data type nor would it affect your code and you would make sure that no invalid date would (or could) be saved.
If this is possible at your stage of development, you may change the data type of the column to
DateTime2, which allows for dates beginning with{1/1/0001 12:00:00 AM}. MSDN documentation here.
确保在您的代码中，您要保存的对象在保存之前设置了有效的 DateTime 值。其他海报已经为此提出了一些建议。
为数据库端的列添加默认值。对我来说，这似乎是最简单、最干净的方法。您既不需要更改数据类型，也不会影响您的代码，并且您将确保不会（或可能）保存无效日期。
如果这在您的开发阶段可行，您可以将列的数据类型更改为
DateTime2，这允许日期以{1/1/0001 12:00:00 AM}. MSDN 文档在这里。
