DATEDIFF - Replace NULL with NOW()

问题:

I got following SQL Query

SELECT
    e.id,
    c.name,
    e.location,
    e.designation,
    e.time_period_from,
    e.time_period_to,
    DATEDIFF(e.time_period_to, time_period_from) AS tenure_in_days
FROM
    employment e
LEFT JOIN
    company c ON (c.id = e.company_id)
LIMIT
    0, 10

This is working perfectly, i have a scenario where time_period_to can have NULL values, in this case, i want to replace it with current date.

Here is what i tried.

SELECT
    e.id,
    c.name,
    e.location,
    e.designation,
    e.time_period_from,
    e.time_period_to,
    DATEDIFF(IF(ISNULL(e.time_period_to), NOW(), e.time_period_from)) AS tenure_in_days
FROM
    employment e
LEFT JOIN
    company c ON (c.id = e.company_id)
LIMIT
    0, 10

This gives me the following error

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DATEDIFF'

Where am i going wrong?

回答1:

Use COALESCE instead:

SELECT
    e.id,
    c.name,
    e.location,
    e.designation,
    e.time_period_from,
    e.time_period_to,
    DATEDIFF(COALESCE(e.time_period_to, NOW()), e.time_period_from) AS tenure_in_days
FROM employment e
LEFT JOIN company c ON (c.id = e.company_id)
LIMIT 0, 10

I guess you wanted DATEDIFF(e.time_period_to, e.time_period_from).

Using LIMIT without explicit ORDER BY may return result dependent on execution plan.

回答2:

You have your parentheses in the wrong place. Instead of specifying e.time_period_from as the second argument to DATEDIFF, you have it as the third argument to IF. It should be:

DATEDIFF(IF(ISNULL(e.time_period_to), NOW(), e.time_period_to), e.time_period_from) AS tenure_in_days

You can also use IFNULL (which is a simpler version of COALESCE, and the name is more mnemonic):

DATEDIFF(IFNULL(e.time_period_to, NOW()), e.time_period_from) AS tenure_in_days