How to do date math on SELECT columns?

The following query works perfectly:

select entry.start_date, max(entry.date) leave
from percent.ldap_history
where entry.id = 'johnsmith' and entry.status = 'active'
group by entry.start_date 

It returns two row each with two dates

entry.start_date    leave
2005-11-14  2011-03-11
2013-11-25  2020-05-11

Instead of returning these two values in a table, I’d like each row to also have the difference of those two dates, in years or days. So I have two questions:

• What’s the general rule for combining columns?
• How do I subtract date values, e.g. start_date from leave?

Answer

1. What’s the general rule for combining columns?

Just do it. You can add columns, subtract them, concatenate them, etc. E.g. SELECT col1 + col2 AS total.

2. How do I substract date values?

Use DATEDIFF.

SELECT DATEDIFF(MAX(entry.date), entry.start_date) AS diff
FROM percent.ldap_history
where entry.id = 'johnsmith' and entry.status = 'active'
group by entry.start_date