时间戳

首页 > 解决方案 > 减去 2 个日期得到天数

sql - 减去 2 个日期得到天数

问题描述

我正在使用优势 sql 数据库，并尝试减去 2 个日期来获得天数。我认为在 ADV SQL 中你可以只减去日期，它会给你天数。

((完成) - (开始日期))

1-23-19 - 1-3-19 = 20 天

标签： sqladvantage-database-server

解决方案

如果两者都是类型，您可以直接在 ADS SQL 中减去日期DATE。如果它们都是TIMESTAMP，则可以使用TIMESTAMPDIFF()withTSI_SQL_DAYS作为间隔。如果一个是 a 另一个是 a DATE，TIMESTAMP则必须将 a 转换TIMESTAMP为 a DATE，然后可以直接相减。

以下是所有这三种选择的示例。

将两列都作为类型DATE：

create table Test (ID integer, StartDate Date, Completed Date);
insert into Test values (1, '2019-01-01', '2019-01-31');
insert into Test values (2, '2019-01-01', '2018-02-28');
select id, startdate, completed, completed - startdate as days from test;

输出：

id  startdate    completed    days      
--- -----------  -----------  ----
1   01/01/2019   01/31/2019     30
2   01/01/2019   02/28/2019     58

将两列都作为类型TIMESTAMP：

create table Test2 (ID integer, StartDate TimeStamp, Completed TimeStamp);
insert into Test2 (ID, StartDate, Completed)
  values (1, CreateTimeStamp(2019, 1, 1, 0, 0, 0, 1),
             CreateTimeStamp(2019, 1, 31, 0, 0, 0, 1));
insert into Test2 (ID, StartDate, Completed)
  values (2, CreateTimeStamp(2019, 1, 1, 0, 0, 0, 1),
             CreateTimeStamp(2019, 2, 28, 0, 0, 0, 1));
select id, startdate, completed, 
  TimeStampDiff(SQL_TSI_DAY, StartDate, Completed) as days from test2;

输出：

id  startdate    completed    days      
--- -----------  -----------  ----
  1 01/01/2019   01/31/2019     30
  2 01/01/2019   02/28/2019     58

有一个 aDATE和一个 a TIMESTAMP：

create table Test3 (ID integer, StartDate TimeStamp, Completed Date);
insert into Test3 (ID, StartDate, Completed)
  values (1, CreateTimeStamp(2019, 1, 1, 0, 0, 0, 1), '2019-01-31');
insert into Test3 (ID, StartDate, Completed)
  values (2, CreateTimeStamp(2019, 1, 1, 0, 0, 0, 1), '2019-02-28');
select id, startdate, completed, 
  Completed - Cast(StartDate AS SQL_DATE) as days from test3;

输出：

id  startdate    completed    days      
--- -----------  -----------  ----
  1 01/01/2019   01/31/2019     30
  2 01/01/2019   02/28/2019     58

推荐阅读