SQL Updating Wrong Value

Navigation
0

I'm creating a QUERY to change every day of the dates recorded in the DB for the last day of the month of that record ... what happens is that when I play QUERY by selecting a record in month 5 (May) it updates the value of the day with 30 ...

follow 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
    
asked by anonymous 03.08.2018 / 16:22

2 answers

1

SOLVED!

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 complete SQL date of the argument date passed to it!

    
03.08.2018 / 18:57
2

Run the following query: 

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

Explaining how the query works: 

DATEDIFF(m, 0, FimValidPre)

DATEDIFF : We are picking up using the m parameter (datepart representing months), the number of months from 1900 in SQL) until our date FimValidPre . So if for example our date is 1423/2018 it will be returned 1423 . 

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

DATEADD : This function has the function of adding dates, however we are saying to add using the months (because of mm as a parameter) . In the second parameter we are passing the result of the DATEDIFF function that is 1423 . We're adding +1 to fetch the next month after our current month in the FimValidPre field (which in our example is 03/08/2018) then our current month is < strong> 08 - August and the next one would be 09 - September . The last parameter we are using to pick up 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 that we have 01/09/2018 00: 00: 00.000 , we are passing the s and we say to subtract a second -1 from our result. From this understanding, we will get the expected result that is the last day of the month at 23:59:59 2018-08-31 23:59:59 .

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

    
03.08.2018 / 16:52
How to add values from a json request in python? Distribute column values, SQL