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 ProviderStackTrace:
at Dapper.SqlMapper.ExecuteScalarImpl[T](IDbConnection cnn, CommandDefinition& command)
at Dapper.SqlMapper.ExecuteScalar(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable1 commandTimeout, Nullable
1 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)
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
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