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

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..

CodePudding user response：

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

CodePudding user response：

Sql Server doesn't have an INTERVAL datatype.
But it's possible to generate a string in that format.

And it's also possible to use a User-Defined Function in a computed column. (As long it's a deterministic UDF)

The example below demonstrates that.

CREATE FUNCTION dbo.fnGetIntervalStamp
(
   @FromDt2 DATETIME2, @ToDt2 DATETIME2
)
RETURNS VARCHAR(30)
WITH SCHEMABINDING
AS
BEGIN
  DECLARE @days INT, @ms BIGINT, @time TIME(3);
  SET @days = DATEDIFF(day, @FromDt2, @ToDt2);
  SET @ms = DATEDIFF_BIG(microsecond, CAST(@FromDt2 as TIME), CAST(@ToDt2 as TIME));
  SET @time = DATEADD(ms, (@ms/10000)00, DATEADD(ss, @ms/10000000, 0));
  RETURN CONCAT(IIF(SIGN(@days)<0,'-',' '), ABS(@days), ' ', @time);
END;

create table interval_test (
    roll_no int not null,
    start_time datetime2,
    end_time datetime2,
    time_interval as dbo.fnGetIntervalStamp(start_time, end_time) persisted
);

insert into interval_test( roll_no, start_time, end_time)
values (452, CURRENT_TIMESTAMP,  '2022-03-02 15:20:52.0960000');

select *
from interval_test

roll_no start_time end_time time_interval

452 2022-02-28 14:31:35.9600000 2022-03-02 15:20:52.0960000 2 00:04:55.613

roll_no	start_time	end_time	time_interval
452	2022-02-28 14:31:35.9600000	2022-03-02 15:20:52.0960000	2 00:04:55.613

Test on db<>fiddle here

CodePudding user response：

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