如何在SQL Server中添加time数据类型列 [英] How do I add time datatype column in SQL server

问题描述

我在sql server 2012中有一个表，其中列名为Duration as Time datatype.i想要根据日期差异更新此列，方法是将差异添加到此持续时间列中。我可以在SP中执行此操作。 br $>

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.

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总是不到24小时。



我尝试过：

Durtion always less than 24 hours.

What I have tried:

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

                       WHERE   ID = @Id

推荐答案

我很久以来一直使用TSQL，很快就想到你需要的东西就像

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

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

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

这篇关于如何在SQL Server中添加time数据类型列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！