How to calculate the corresponding Saturday from one column of dates and enter them into another column

existing column of DATE data is date plus time, for example: 2018-01-01 15:00:30

now you want to calculate the date in the DATE column to the corresponding Saturday and enter it into the WEEKEND column in order. As long as the date is not time, how to write the SQL statement?

how do I change the following statement or how should I write it?
select subdate (curdate (), date_format (curdate (),"% w")-6)

Mar.04,2021

Business logic should be written in the program. Try not to use the database for logical operations. The database mainly accesses data, makes reports and so on.

shit, am I wrong? Business logic should not be written in the program? If you write the database logic, you will have some worries about refactoring later. -hide my answer, dry chicken feathers?

Ps, unwanted book library logic can also be: update table set saturday = SELECT date (myDate + INTERVAL 7-weekday (myDate) DAY);


you can try the following sql, but the database is not suitable for this operation.

UPDATE table_name SET WEEKEND = subdate(date_format(`DATE`,'%Y-%m-%d'),date_format(`DATE`,'%w')-6)

mysql date operation function learn

DATE_FORMAT(DATE_ADD(`date`, INTERVAL 6-DATE_FORMAT(`date`,'%w') DAY), '%Y-%m-%d')

if you need to save next Saturday when today is Saturday, add interval to the middle if .

Menu