SQL Updating Wrong Value

Question

I am creating a QUERY to change every day of the dates registered in the BD to the last day of the month of that record... what happens is that when I play the QUERY selecting a record in the month 5 (May) it updates the value of the day with 30...

follows the code:

UPDATE CPSCli 
SET FimValidPre = 
CASE MONTH(FimValidPre)
    WHEN (1 OR 3 OR 5 OR 7 OR 8 OR 10 OR 12) THEN
        DATE_FORMAT(FimValidPre, '%Y-%m-31')
    WHEN (2) THEN
        DATE_FORMAT(FimValidPre,CONCAT('%Y-%m-',IF(YEAR(FimValidPre) % 4 = 0,29,28)))
    ELSE
        DATE_FORMAT(FimValidPre, '%Y-%m-30')
    END

Answer 1

Run the following query:

UPDATE CPSCli
   SET FimValidPre = DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, FimValidPre) + 1, 0))

Explaining the operation of the query:

DATEDIFF(m, 0, FimValidPre)

DATEDIFF: We’re taking through the parameter m (datepart representing months), the number of months from 1900 (last year of research accepted in SQL) to our date Fimvalidpre. So if for example our date is 03/08/2018 will be returned 1423.

DATEADD(mm, DATEDIFF(m, 0, FimValidPre) + 1, 0)

DATEADD: This function has the function of summing dates, however we are saying to add using the months as reference (because of mm as parameter). In the second parameter we are passing the function result DATEDIFF that is 1423. We’re adding up +1 to seek the next month after our current field month Fimvalidpre (which in our example is the date 03/08/2018) then our current month is 08 - August and the next would be 09 - September. The last parameter we are using to catch the first day of next month, which would be 01/09/2018.

DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, FimValidPre) + 1, 0))

DATEADD: With the result we possess 01/09/2018 00:00:00.000, we are passing as parameter the seconds s, and say to subtract a second -1 of this our result. From this understanding, we will obtain the expected result which is the last day of the month at 23:59:59 hours 2018-08-31 23:59:59.000.

If you need to filter by any specific criteria, be sure to add the clause WHERE in the query.

Answer 2

RESOLVED!

I solved it in a much simpler way than the one I posted!

follows the result:

UPDATE CPSCli set FimValidPre = LAST_DAY(FimValidPre)

LAST_DAY returns the last day of the month with the full date in SQL format of the argument date passed to it!