CODEWITHSUNDEEP
phpmysqlsql
ndjustin20
ndjustin20

Reputation: 105

Compare dates in MySQL using CASE

I am stuck on trying to use some sql to figure out if a date is within 90, 60, or 30 days. I think I am close but can't get it to work properly. I have a column named boosterDate and it's a date in MySQL. That column I want to check to see if it is within 90, 60, or 30 days from DATE(Now()) and then set the column boosterWithinDays to either 90, 60, or 30 days. The table name is service.

Here is where I am so far:

$sql = "SELECT
     CASE WHEN DATE(boosterDate) < ADDDATE(DATE(NOW()),INTERVAL 90 DAY) THEN 90
     WHEN DATE(boosterDate) < ADDDATE(DATE(NOW()),INTERVAL 60 DAY) THEN 60
     WHEN DATE(boosterDate) < ADDDATE(DATE(NOW()),INTERVAL 30 DAY) THEN 30
     ELSE NULL END FROM service;";

Upvotes: 2

Views: 2944

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

This is your query:

SELECT (CASE WHEN DATE(boosterDate) < ADDDATE(DATE(NOW()), INTERVAL 90 DAY) THEN 90
             WHEN DATE(boosterDate) < ADDDATE(DATE(NOW()), INTERVAL 60 DAY) THEN 60
             WHEN DATE(boosterDate) < ADDDATE(DATE(NOW()), INTERVAL 30 DAY) THEN 30
             ELSE NULL
       END)
FROM service

My guess is that your problem is that this is always returning 90. The simple reason is that when the second and third conditions are true, then so is the first. The case statement stops at the first matching statement.

So, reverse the conditions:

SELECT (CASE WHEN DATE(boosterDate) < ADDDATE(DATE(NOW()), INTERVAL 30 DAY) THEN 30
             WHEN DATE(boosterDate) < ADDDATE(DATE(NOW()), INTERVAL 60 DAY) THEN 60
             WHEN DATE(boosterDate) < ADDDATE(DATE(NOW()), INTERVAL 90 DAY) THEN 90
             ELSE NULL
       END)
FROM service

Upvotes: 6

Related Questions