How do I add a TimeSpan to a time column in SQL Server

1

I want to do something like the following:

update AutoTagUse set TimeActive = (TimeActive + @OrigTimeActive) where AutoTagUseId = @AutoTagUseId

Where TimeActive is a TIME(0) column in SQL Server, and OrigTimeActive is a TimeSpan variable (in C#).

I'm trying to do this so I can have multiple services updating this value in the database simultaneously without have to lock things down with transactions.

The SQL Server entry to create the column is:

[TimeActive] TIME (0)       
    CONSTRAINT [DF_AutoTagUse_TimeActive] DEFAULT ('00:00:00') NOT NULL,

What I am now trying is:

TimeSpan difference = TimeActive - origTimeActive;
conn.ExecuteScalar("update AutoTagUse set TimeActive = dateadd(SECOND, @OrigTimeActive, TimeActive) where AutoTagUseId = @AutoTagUseId", 
                        new { AutoTagUseId = AutoTagUseId, OrigTimeActive = difference }, transaction);

And I am getting:

System.Data.SqlClient.SqlException
HResult=0x80131904
Message=Argument data type time is invalid for argument 2 of dateadd function.
Source=.Net SqlClient Data Provider

StackTrace:
at Dapper.SqlMapper.ExecuteScalarImpl[T](IDbConnection cnn, CommandDefinition& command)
at Dapper.SqlMapper.ExecuteScalar(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable1 commandTimeout, Nullable1 commandType)
at LicenseLibrary.DataObjects.AutoTagUse.Update(IDbConnection conn, IDbTransaction transaction) in C:\git\Store\LicenseLibrary\DataObjects\AutoTagUse.cs:line 171
at TestLibrary.DataObjects.TestAutoTagUse.<>c.b__2_0(IDbConnection conn) in C:\git\Store\TestLibrary\DataObjects\TestAutoTagUse.cs:line 59
at LicenseLibrary.Database.AzureDataAccess.<>c__DisplayClass11_0.b__0() in C:\git\Store\LicenseLibrary\Database\AzureDataAccess.cs:line 104
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.<>c__DisplayClass1.b__0()
at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)

sql-server
ado.net
timespan
asked on Stack Overflow Sep 15, 2018 by David Thielen • edited Sep 16, 2018 by marc_s

2 Answers

1

Being time(0), I'm assuming your interval is SECONDS

You can use DATEADD()

Example

Update AutoTagUse 
   set TimeActive = dateadd(SECOND,@OrigTimeActive,TimeActive) 
 Where AutoTagUseId = @AutoTagUseId
answered on Stack Overflow Sep 15, 2018 by John Cappelletti
1

In general, if you're wanting to store a time span, the time data type in SQL Server is not suitable. Because it's designed for storing times of day, not time spans. This means that there's no support for adding two of these values together (it doesn't make sense to add 4pm and 8pm together). Nor does it support negative values or values greater than 24 hours.

For all of these reasons, I'd usually recommend instead using a numeric data type with a clear indication given in its name of the intended units. (I usually prefer an int type in the smallest granularity required, others may choose to use a decimal of some sort)

E.g. I'd use SecondsActive int as the column here.


User contributions licensed under CC BY-SA 3.0