• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

SQL Server, Creating a field with the difference between two datetimes in minutes.

I'm trying to create a table using sql server with two datetime fields,  time_in, and time_out and a third field for time elapsed which I want to be the number of minutes between time_out minus time_in.  Is there a way to declare time_elapsed that will automatically calculate it?

Thanks!

create table pracdata (
  id                  int identity(1,1),
  time_in        datetime not null,
  time_out      datetime not null,
  time_elapsed int,
  deleted        bit not null
);
0
padmasambhava
Asked:
padmasambhava
1 Solution
 
dbbishopCommented:
time_elapsed int = datediff(mi, time_in, time_out),
0
 
dbbishopCommented:
Sorry, should be:
time_elapsed as datediff(mi, time_in, time_out),
0
 
Chris MangusDatabase AdministratorCommented:
Try:

CREATE TABLE [dbo].[pracdata](
      [id] [int] IDENTITY(1,1) NOT NULL,
      [time_in] [datetime] NOT NULL,
      [time_out] [datetime] NOT NULL,
      [deleted] [bit] NOT NULL,
      [time_diff]  AS (datediff(minute,[time_in],[time_out]))
)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
YveauCommented:
Use a trigger to set the time_elapsed column value for each insert and update

create trigger TimeElapsed
on PracData
for insert, update
as
begin
    update pracdata
    set time_elapsed = datediff(mi, I.time_in, I.time_out)
    from pracdata P
    inner join inserted I
    on P.id = I.ID
end
go

insert into pracdata (time_in, time_out, deleted) values ('13:00', '14:00', 0)
insert into pracdata (time_in, time_out, deleted) values ('13:00', '15:00', 0)
select * from pracdata
-->>
id          time_in                 time_out                time_elapsed deleted
----------- ----------------------- ----------------------- ------------ -------
1           1900-01-01 13:00:00.000 1900-01-01 14:00:00.000 60           0
2           1900-01-01 13:00:00.000 1900-01-01 15:00:00.000 120          0

update pracdata set time_in = '13:30'
select * from pracdata
-->>
id          time_in                 time_out                time_elapsed deleted
----------- ----------------------- ----------------------- ------------ -------
1           1900-01-01 13:30:00.000 1900-01-01 14:00:00.000 30           0
2           1900-01-01 13:30:00.000 1900-01-01 15:00:00.000 90           0


Hope this helps ...
0
 
dbbishopCommented:
Also, FYI, this will not round the results, but returns, in effect, the floor() value so, if the elapsed time is actually 23-minutes, 59-seconds, the result of datediff will be 23. If you need (or want) fractional part, you can do:
elapsed  as CAST(datediff(s, date_in, date_out) AS DECIMAL) / 60
0
 
padmasambhavaAuthor Commented:
Thanks DBBishop, and everyone else.

Peter
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now