Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table in sql server 2012,with column named Duration as Time datatype.i want to update this column base on difference of dates, by adding the difference to this duration column.how can I do this in SP.


SQL
ID    StartDate               EndDate                 Duration
1     2017-02-27 09:10:35     2017-02-27 09:25:35      00:15
2     2017-02-27 09:26:35   2017-02-27 09:36:35        00:25

Durtion always less than 24 hours.

What I have tried:

SQL
UPDATE  EmployeeDuration
                       SET     TimeSpan = CAST(@Durtion AS TIME)

                       WHERE   ID = @Id
Posted
Updated 27-Feb-17 18:20pm
Comments
Nitinjain0101 28-Feb-17 0:06am    
Which data type is your @Duration variable?
Sajid227 28-Feb-17 8:01am    
its time datatype

1 solution

I havnt used TSQL for a long time, a very quick thought suggests you need something along the lines of

SQL
CREATE PROC sp_UpdateDuration (
    @id integer)
AS
    BEGIN
	UPDATE EmployeeDuration E
	    Set E.Duration = DateDiff(day, E.StartDate, E.EndDate)
	    Where E.id = @id;
    END
GO


and you call it as

SQL
EXEC dbo.sp_UpdateDuration @id = (Row Id);


although I get the feeling you're better off using a cursor if you wish to update more than one row
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900