CODEWITHSUNDEEP
sqlsql-serversql-server-2008c#-4.0c#-3.0
user3441151
user3441151

Reputation: 1910

SqlDbType.Time overflow. Value is out of range

I want to store time in a table and I take time(7) as datatype for storing the time. When I subtract two DateTimes, I got "1.18:36:36.7484253" as result in my TimeSpan variable. But the problem is that when I insert this value in my table, I got this error:

SqlDbType.Time overflow. Value '1.18:36:36.7484253' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.

I knew about this error and my question is what datatype I can use for this. I am using SQL Server 2008 and C#.

Upvotes: 8

Views: 8121

Answers (2)

Julio Spader
Julio Spader

Reputation: 349

SQL Server stores the time without days. The valid range should be between 00:00:00.0000000 and 23:59:59.9999999 as in the message.

For save days, you can convert TimeSpan to Long to method Ticks or sum the minutes and seconds.

Upvotes: 6

Marco Antonio
Marco Antonio

Reputation: 21

to resolve that problem, you need to do the following:

HI, you need to create a new TimeSpan Variable.

//////// Calculation for TimeStamp //////////

                DateTime fechaAux       = DateTime.Now;
                DateTime fechaIngresoAM = new DateTime(fechaAux.Year, fechaAux.Month, fechaAux.Day,  8, 30, 00);


                TimeSpan auxIngresoAM   = ingresoAM.Subtract(fechaIngresoAM);
                TimeSpan resultadoRetrasoIngresoAM = new TimeSpan(auxIngresoAM.Hours, auxIngresoAM.Minutes, auxIngresoAM.Milliseconds);



                command.Parameters.Add(new SqlParameter("@retraso_ingreso_am", SqlDbType.Time));
                command.Parameters["@retraso_ingreso_am"].Value = resultadoRetrasoIngresoAM;

                command.ExecuteNonQuery();

It will work as you expect.

Upvotes: 0

Related Questions