Alternative of INTERVAL DAY() TO SECONDS() SQL Server

Question

I have a table definition as follows,

create table interval_test (
    roll_no number not null,
    start_time timestamp(6),
    end_time timestamp(6),
    time_interval INTERVAL DAY (9) TO SECOND (6) generated always as (end_time - start_time)
)

on inserting the data..

insert into interval_test( roll_no, start_time, end_time)
values (452, CURRENT_TIMESTAMP,  '02-03-22 1:20:52.096000000 PM');

This is the output that is generated

452(roll_no)    28-02-22 1:17:07.858000000 PM(start_time)   02-03-22 1:20:52.096000000 PM(end_time) +02 00:03:44.238000(time_interval)

Here the time_interval column is calculated and showing the interval +02 00:03:44.238000 in day to second format.

Is there a way I can achieve same functionality in SQL Server?

I am new to sql server..

Answer 1

You can use DATEDIFF(), there is no equivalent of INTERVAL in SQL Server.

For instance:

SELECT DATEDIFF(day, '2017/08/25', '2011/08/25') AS DateDiff;

would return you

-2192

http://sqlfiddle.com/#!18/9eecb/7793

Answer 2

Although there is no interval type in SQL Server, you can actually use the datetime type to also store intervals.
A 0 zero interval is presented as 1900-01-01 00:00:00.000
datetime supports not only subtraction operations but also addition operations.

declare @dttm_1 datetime = '2022-02-25 10:58:21'
declare @dttm_2 datetime = '2022-02-27 13:02:07'
declare @dttm_interval datetime

select cast(0 as datetime) as "cast(0 as datetime)"
-- 1900-01-01 00:00:00.000

set @dttm_interval = @dttm_2 - @dttm_1

select @dttm_interval as "@dttm_interval = @dttm_2 - @dttm_1"
-- 1900-01-03 02:03:46.000

select @dttm_1 + @dttm_interval as "@dttm_1 + @dttm_interval"
-- 2022-02-27 13:02:07.000

select datediff(hour   ,0 ,@dttm_interval) as hours   -- 50
      ,datediff(minute ,0 ,@dttm_interval) as minutes -- 3003
      ,datediff(second ,0 ,@dttm_interval) as seconds -- 180226

Fiddle