Mysql Question posted in
The official MySQL documentation can be found here.

Whether error will throw if we use null date in DATEDIFF() in MYSQL

I have a query to be run in one IAM tool where we are comparing last passwordchangedate and now.
i just needs to know whether below query will throw error if we pass null value inside DATEDIFF()?

SELECT DISTINCT 
u.username AS UName,
u.statuskey AS SK,
CASE 
    WHEN DATEDIFF(NOW(), u.createdate) >= 30 
         AND DATEDIFF(Now(),a.LASTPASSWORDCHANGE) <= 30 
         AND u.customproperty18 !='Y' 
         AND u.customproperty63 IS NULL THEN 'Inactive'
    ELSE u.customproperty17
END AS CP17
FROM 
users u
WHERE
  ((DATEDIFF(Now(),u.createdate) > 30 
   AND DATEDIFF(Now(),a.LASTPASSWORDCHANGE) <= 30

i just needs to know what will happen if we pass null in DATEDIFF and what is solution for this.
what i needs to modify in my above query

Tags:

2

Answers


  1. 0

    i just needs to know whether below query will throw error if we pass null value inside DATEDIFF()?

    No. The documentation explicitly tells that

    DATEDIFF(expr1,expr2)

    …

    This function returns NULL if expr1 or expr2 is NULL.

    So the case when the argument value is NULL is a regular feature and not an error. Neither an error nor a warning is generated. And your progran won’t receive any signal that it’d throw an exception.

    Login or Signup to reply.
  2. 0

    Use a wrapper of condition statement for NULL Date validation as follows:

    SELECT DISTINCT 
u.username AS UName,
u.statuskey AS SK,
CASE 
  WHEN(u.createdate is not NULL AND a.LASTPASSWORDCHANGE is not NULL)  THEN 
    CASE 
        WHEN DATEDIFF(NOW(), u.createdate) >= 30 
            AND DATEDIFF(Now(),a.LASTPASSWORDCHANGE) <= 30 
            AND u.customproperty18 !='Y' 
            AND u.customproperty63 IS NULL THEN 'Inactive'
        ELSE u.customproperty17
    END 
END AS CP17
FROM 
users u
WHERE (
  (u.createdate is not NULL AND a.LASTPASSWORDCHANGE is not NULL)
  AND DATEDIFF(Now(),u.createdate) > 30 
  AND DATEDIFF(Now(),a.LASTPASSWORDCHANGE) <= 30 
)

    I think it should work.

    Thank you

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search