当前位置: 动力学知识库 > 问答 > 编程问答 >

tsql - Re: Computed column by subtracting two other columns in SQL Server

问题描述:

I have the following columns in my table name Trip;

trip_no (PK, int, not null),

ID_company (FK, int not null),

plane (char(25), not null),

town_from (char(25) not null),

town_to (char(25) not null),

time_out (datetime, not null),

time_in (datetime, not null),

time_out_only (time(0), null),

time_in_only (time(0) null),

time_in_flight (time(0) null);

For the last three columns My first row would look like this;

time_out_only time_in_only time_in_flight

14:30:00 17:50:00 null

I want the time in flight to compute to 02:20:00. So I tried the the code to time_in_flight equal to time_in_only less time_out_only, as follows,

ALTER TABLE Trip

SET time_in_flight = time_in_only - time_out_only;

The error message I receive is

"Msg 102, Level 15, State 1, Line 2. Incorrect syntax near

'Time_in_Flight".

Unless I am incorrect in is using SET and I should be using a different function, I am confused as to where I am going wrong. However I thought this syntax worked for me before.

Can anyone guide me on where I am going wrong here, and help me figure out how to subtract the two columns using some function and formula.

Thanks Josie

网友答案:

First of all there is no substract operator for time. So using

SELECT time_in_only - time_out_only
FROM trip

you will get:

Operand data type time is invalid for subtract operator.

If you want computed column you can first DROP column and recreate it:

ALTER TABLE Trip
DROP COLUMN time_in_flight;

ALTER TABLE Trip
ADD time_in_flight 
  AS  DATEADD(mi, DATEDIFF(mi, time_out_only,time_in_only), CAST('00:00:00' AS TIME));

LiveDemo

If you don't want to recreate table structure, and calculate manually use UPDATE:

UPDATE Trip
SET time_in_flight = DATEADD(mi, 
                  DATEDIFF(mi, time_out_only,time_in_only), CAST('00:00:00' AS TIME));

LiveDemo2

Last possible solution is to create view:

CREATE VIEW vw_Trip
AS
SELECT -- cols,
   time_in_flight = DATEADD(mi,
                 DATEDIFF(mi, time_out_only,time_in_only), CAST('00:00:00' AS TIME))
FROM Trip
分享给朋友:
您可能感兴趣的文章:
随机阅读: