SqlDbType.Time overflow. Value is out of range

Question

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

One simple option would be to store the number of ticks, milliseconds, seconds or whatever unit of granularity you want, just as an integer. — Jon Skeet
– Jon Skeet, Commented Oct 3, 2015 at 6:36
Possible duplicate of Storing TimeSpan with Entity Framework Codefirst - SqlDbType.Time overflow — Ashraf Sada
– Ashraf Sada, Commented Feb 16, 2019 at 14:03

H. Pauwelyn · Accepted Answer · 2019-01-08 13:33:28Z

6

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.

edited Jan 8, 2019 at 13:33

H. Pauwelyn

14.4k28 gold badges93 silver badges164 bronze badges

answered Jun 13, 2017 at 12:59

Julio Spader

3494 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marco Antonio · Accepted Answer · 2016-12-07 14:32:17Z

0

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.

answered Dec 7, 2016 at 14:32

Marco Antonio

211 bronze badge

1 Comment

Damien_The_Unbeliever Over a year ago

"It will work as you expect." No, it really won't. The OP is working with a timespan ~42 hours long. The SQL Server time data type is designed to hold a time of day, not a time span. As such, it doesn't support any values larger than 24 hours (nor does it support negatives, nor addition, etc, that you would also expect a time span data type to support)

Collectives™ on Stack Overflow

SqlDbType.Time overflow. Value is out of range

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related