DATEDIFF-用NOW（）替换NULL [英] DATEDIFF - Replace NULL with NOW()

问题描述

我遵循了SQL查询

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

这非常正常，我有一种情况，其中 time_period_to 可以具有 NULL 值，在这种情况下，我想将其替换为当前日期。

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.

这是我尝试过的。

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

这给了我以下错误

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

的调用中的参数计数不正确

我要去哪里了？

Where am i going wrong?

推荐答案

使用 COALESCE 代替：

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

我想您想要 DATEDIFF（e.time_period_to，e.time_period_from）。

使用 LIMIT 而不显式 ORDER BY 可能会返回结果取决于执行计划。

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

这篇关于DATEDIFF-用NOW（）替换NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！