Subtract 2 dates to get number of Days

Question

I am working in advantage sql database, and trying to subtract 2 dates to get number of days. I thought in ADV SQL you could just subtract the dates and it would give you the num of days.

((completed) - (startdate))

1-23-19 - 1-3-19 = 20 days

Answer 1

You can directly subtract dates in ADS SQL if both are of type DATE. If they're both TIMESTAMP, you can use TIMESTAMPDIFF() with TSI_SQL_DAYS as the interval. If one is a DATE and the other a TIMESTAMP, you have to convert the TIMESTAMP to a DATE, and then you can directly subtract.

Here are examples of all three of those alternatives.

With both columns as type 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;

Output:

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

With both columns as type 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;

Output:

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

With one a DATE and one 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;

Output:

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

Answer 2

I think timestampdiff() should do what you want:

timestampdiff(SQL_TSI_DAY, startdate, completed)

Answer 3

This is a pretty unusual DB to get questions on - docs suggest (https://devzone.advantagedatabase.com/dz/webhelp/advantage10/index.html?master_supported_scalar_functions.htm ) that you need :

TIMESTAMPDIFF(SQL_TSI_DAY,completed, startdate)

Edit : including the cast

TIMESTAMPDIFF(SQL_TSI_DAY,completed, cast(startdate as SQL_TIMESTAMP))

Docs :

TIMESTAMPDIFF( interval, timestamp1, timestamp2 )

Returns number of integer intervals based on subtracting timestamp1 from timestamp2. Interval values supported are: SQL_TSI_FRAC_SECOND, SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR.