DateTime issue in SQL Server

Question

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).

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 Datetime type 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?

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?

Solution 1

I would assume that Servicios has DateTime property which is not being set.

DateTimes are not nullable, and when they're not set, they default to {1/1/0001 12:00:00 AM}.

Upon calling db.SubmitChanges(), you're attempting to insert a value into a DateTime column which is a lower value that SQL SERVER supports as seen in your exception.

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Solution 2

Be aware that MS SQL Server's DateTime and the C# DateTime have different 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}.

There are 3 approaches that come to my mind:

1. 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.

2. 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.

3. 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.

Solution 3

I cant understand why you are converting a datetime into 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.

Solution 4

According to me,it would be as follow:

datetime.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

View more solutions

Comments

[NeoVe]

I guessed that, i mean, should i set that property on the db itself? On the datetime table?

[Khan]

It depends on what makes more sense. It seems like your options would be to make the field nullable, or set it the current date.

[NeoVe]

On datetime 'default' row i got (getdate()), i try to delete it, but when i update it doesn't changes, btw i made the table nullable, still nothing

[Khan]

@KristianKoci after making the column nullable, you must make sure that the edm reflects the change. Your DateTime property should be nullable as well now.