Microsoft SQL Server Column Function

I'm trying to set the default value in a table I'm creating to:
(dateadd(s,TotalTime*60, StartTime))

Where TotalTime is another column and is a Real.
And StartTime is another column and is a DateTime.

Syntax help please.

Thanks,
Ni
KnightWhoSaysNiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
micropc1Connect With a Mentor Commented:
I don't believe that's possible since SQL won't let you use field names in the default value.

You'll probably need to create a trigger to handle this. Something like...

CREATE TRIGGER
ON tableName
AFTER INSERT,UPDATE
AS
UPDATE tableName SET dateColumn = dateadd(s,TotalTime*60, StartTime) WHERE primaryKeyField IN (select primaryKeyField from inserted where dateColumn IS NULL)

Open in new window

0
 
Barry CunneyConnect With a Mentor Commented:
A computed column may also be an option - Of course it depends on your requirment
Do you want the field to have a default value that can be overwritten if so desired by an app/users
If not a computed column might be an option
ALTER TABLE yourtable ADD yourcolumn (dateadd(s,TotalTime*60, StartTime))
0
 
Anthony PerkinsConnect With a Mentor Commented:
As suggested previously you need a computed column, just a minor correction (no points please), for a computed column the AS is required as in:
ALTER TABLE yourtable ADD yourcolumn AS (DATEADD(s,TotalTime * 60, StartTime))
0
 
micropc1Commented:
Yes - as bcunney said, It depends on your requirement. Since you said you want to set a "default" value I assume you're wanting to be able to insert data into the column.  You can't insert data into a computed column using an INSERT or UPDATE statement. If that's what you want you'll need a trigger. If you don't need to insert data then the computed column is the better and simpler option.
0
 
KnightWhoSaysNiAuthor Commented:
Thanks for the input.  I think either solution provided would resolve the issue, but I went a little different direction.

I decided to use a stored procedure to perform the insert and declare the calculated variables in the stored procedure.

BEGIN TRY
      DECLARE @StartTime datetime;
      DECLARE @EndTime datetime;
      SET @EndTime = GETDATE();
      SET @StartTime = DATEADD(SS,@TotalTime*-60,@EndTime);
0
All Courses

From novice to tech pro — start learning today.