Finding SQL Days between two dates with nulls

2
votes

I have two columns: rental_date and actual_retdate. I need to find the amount of days between the actual_retdate and rental_date. The actual_retdate can be null for some instances so I would like to find the amount of days between today's date and the rental date in those situations.

Currently I have:

select rental_date, actual_retdate, actual_retdate - rental_date as 'Daysbetween' 
from rental_agreement

This gives me answers of: 

Rental_date     actual_retdate  Daysbetween
2014-07-04  2014-07-11  7
2016-05-06  2016-05-08  2
2016-08-07  2016-09-07  100
2015-02-02  2015-02-10  8
2015-10-10  2015-10-15  5
2015-08-07  2015-08-17  10
2017-02-04  NULL            NULL
2016-07-08  2016-07-16  8
2017-03-02  NULL            NULL
2015-03-15  2015-04-15  100
3
mysqlsql
In MySQL, the subtraction operator isn't guaranteed to return a number days between two date values. It may work in some cases (when the two dates are in the same year and month), but doesn't return a correct value in the more general case. (Note the rows with a return of 100. In MySQL, we can get a difference in days using TIMESTAMPDIFF(DAY,dateexpr1,dateexpr2). – spencer7593

3 Answers

3
votes

Sounds like you could coalesce the actual_retdate with NOW().

It would look something like:

SELECT rental_date, 
    actual_retdate, 
    COALESCE(actual_retdate,NOW()) - rental_date as 'Daysbetween' 
FROM rental_agreement

COALESCE basically returns the first non-null value in the list. So if actual_retdate is NULL, it will return the value for NOW().

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_coalesce

Edit: Since Spencer mentioned subtraction not being completely reliable as a way to get the difference in number of days, here's an updated snippet using his suggested TIMESTAMPDIFF.

SELECT rental_date, 
    actual_retdate, 
    TIMESTAMPDIFF(DAY,COALESCE(actual_retdate,NOW()),rental_date) as 'Daysbetween' 
FROM rental_agreement
1
votes

You are looking for coalesce():

select rental_date, actual_retdate,
       coalesce(actual_retdate, curdate()) - rental_date as Daysbetween 
from rental_agreement
1
votes

Use COALESCE to provide another value for the case when a value is NULL:

SELECT
    rental_date,
    actual_retdate
    COALESCE( actual_retdate, CURDATE() ) AS actual_retdate_or_today,
    COALESCE( actual_retdate, CURDATE() ) - rental_date AS days_between
FROM
    rental_agreement

CURDATE() returns the current date. I assume that actual_retdate is a date column rather than a datetime column, as the results may be unexpected. Also consider using DATEADD instead of the - operator for performing date-arithmetic calculations.

The reason I repeated the COALESCE( actual_retdate, CURDATE() ) expression is because in SQL you cannot reference a column expression in another column - you must either recompute the result or wrap it in an outer-query.