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

MYSQL insert row in the table only if the day of execution is the last day of the month

I’m trying to write a very simple query that inserts a count of accounts in my ns_monthly_count table. The catch is, I want this query to execute only if the day of execution is the last day of the month. So if I run this today on ‘2024-02-23’, It should do nothing, but If i do it on 29th, It should run the query. I’ve tried just adding a where clause to check if the current day is equal to the last day of the month but when the query executes I get a row added in the table with value 0 in my column.

 INSERT INTO ns_monthly_count (count)
                SELECT count(*)
                FROM ns_accounts a
                WHERE a.statusID = 1 and curdate() = last_day(curdate())

This is the query. I’d appreciate any help!

Tags:

2

Answers


  1. 0

    COUNT(*) always returns a value. The WHERE condition just makes it return 0 when the condition doesn’t match any rows.

    Use HAVING to filter the result set rather than the rows counted.

    INSERT INTO ns_monthly_count (count)
SELECT count(*)
FROM ns_accounts a
WHERE a.statusID = 1
HAVING curdate() = last_day(curdate())
    Login or Signup to reply.
  2. 0 
    INSERT INTO ns_monthly_count (count)
select nr from (
    SELECT count(*) as nr
    FROM ns_accounts as a
    WHERE a.statusID = 1
)as t1 where curdate() = last_day(curdate());
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search