Ibrahim Azhar Armar Ibrahim Azhar Armar - 4 years ago 166
SQL Question

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?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download