T-SQL check constraint for .NET TimeSpan?

Question

I have a nvarchar(max) column in a sql server 2005 table which is used for storing string representations of .NET TimeSpan objects. Sometimes the table is manually edited. I want to add a check constraint to verify that the string can be parsed by TimeSpan.Parse(). How should I do this? I guess I could use one of the methods for enabling regex in sql server, but I'd like to find an easier way if there is one!

Answer 1

A much better way to store .Net Timespans is in an int column using the TimeSpan's .Ticks property.

Of course, that breaks the manual table edits. But manual table edits are evil anyway. The best way to be sure TimeSpan.Parse() works or that you have a valid value is provide a client app using the function in question to accomplish your edits.

Finally, if you must do this try building a clr user-defined function that tests using TimeSpan.Parse(). Then see if you can include that function in your constraint (I really don't know off the top of my head if udf's (clr udf's in particular) are allowed there).

Answer 2

I agree with Joel, that if at all possible you should try to get rid of the direct table edits. I'd also add that it's usually a bad idea to couple your database so tightly to your front end code like that. Store the data in a way that works best for the database and convert it as necessary for any front-end code.

If you can't, then maybe this first attempt will get you close. It doesn't allow the full precision that Timespan.Parse does because the ISDATE() function only accepts times to the nearest 1000th of a second. Maybe you can build on it though. Strip out the second fractions as I did the days and check those separately. It's a bear of an expression though.

CREATE TABLE dbo.Test_Timespan
(
    my_string NVARCHAR(MAX) NOT NULL,
    CONSTRAINT CK_Test_Timespan_my_string CHECK (CAST(SUBSTRING(RTRIM(LTRIM(my_string)), 1, CHARINDEX('.', RTRIM(LTRIM(my_string))) - 1) AS INT) BETWEEN -10675199 AND 10675199 AND ISDATE(SUBSTRING(RTRIM(LTRIM(my_string)), CHARINDEX('.', RTRIM(LTRIM(my_string))) + 1, LEN(my_string) - CHARINDEX('.', RTRIM(LTRIM(my_string))))) = 1)
)