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
);
padmasambhavaAsked:
Who is Participating?
 
dbbishopConnect With a Mentor Commented:
Sorry, should be:
time_elapsed as datediff(mi, time_in, time_out),
0
 
dbbishopCommented:
time_elapsed int = 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.