Re: Computed column by subtracting two other colum

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