SqlDateTime overflow Exception

Question

Hi, I am trying to insert a time only value, but get the following error

• ex {"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM."} System.Exception

From the front end, the time is selected using the "TimeEdit" control, with the up and down arrows. The table in SQL Server has the fields set as smalldatetime. I only need to store the time. I use the following to return data to the app

select id,CONVERT(CHAR(5),timeFrom,8)as timeFrom,CONVERT(CHAR(5),timeTo,8)as timeTo FROM dbo.Availability where id = @id and dayName = @weekday

How do I pass time only to the table?

Edit ~ Solution As per Euardo and Chris, my solution was to pass a datetime string instead of a time only string. I formatted my result as per Time Format using "g".

Thanks

Answer 1

there is no such thing as Time in SQL, there is only DateTime.

For your purpose, I would use something like this to only return the time portion.

SELECT (GETDATE() - (CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)))

where GETDATE() is the datetime you want to filter.

When setting the time in the database, you will have to add '01/01/1901' or '01/01/1753' to the time.

Dont use CAST and Convert to varchar when working with datetime, its slow. Stick to floating numerical operations.

Answer 2

Pick a date that is in the range(ie, 1/1/1970) and use it for everything you insert.

Answer 3

You can set the date to 1/1/1753 wich is date min value for datetime in MSSQL and then add the hour you want to store. Of course you have to consider this every time you need to get the value, but you can wrap that with some helpers. Or you can use MSSQL 2008 and use the new TIME datatype.

Answer 4

If you are only keeping track of the time, think about storing it in an int as an offset from midnight in whatever granualarity you need (seconds, minutes, hours, ...). You can then convert it to a TimeSpan in your code using the appropriate TimeSpan.From??() method. To go back the other way, you can use TimeSpan.Total?? and truncate if need be. If you need to do manual queries you can write a SQL function that will convert hours/mins/seconds to the equivalent offset.

I prefer this over using a datetime and picking an arbitrary day as it makes the purpose of the field clearer, which reduces confusion.