How to calculate difference date mysql?-CodePudding

I want to write start date and end date difference dynamically.

For example date is 27.07.2022 now. So, for july 2022-07-01 and 2022-07-31.

I use DATE_SUB("2022-07-31, INTERVAL 31 DAY) but july is not dynamically. IF I use MONTH(CURDATE()); only gives month. Also I must to change for June (2022-06-30 , INTERVAL 30 DAY)

I need diffrence current month in dynamically.

How can I access 2022-07-01 and 2022-06-01(now month and before now month) ?

CodePudding user response：

For this, you can use LAST _DAY(), something like this:

SELECT LAST_DAY('2022-07-22'); // Will give you 2022-07-31
SELECT date_add(date_add(LAST_DAY('2022-07-22'),interval 1 DAY),interval -1 MONTH) AS first_day; // Will give you 2022-07-01

CodePudding user response：

Please refer to the below SQL script.

SELECT *
FROM   record
WHERE  1 = 1
       AND Date_format(operation_time, '%Y%m') = Date_format(Curdate(), '%Y%m')
ORDER  BY id DESC

CodePudding user response：

Thsi woild be a possiblity with LAST_DAY and DATE_ADD

SELECT date_add(date_add(LAST_DAY(curdate()),interval 1 DAY),interval -1 MONTH) AS first_day,
LAST_DAY(curdate()) as last_day;

first_day  | last_day  
:--------- | :---------
2022-07-01 | 2022-07-31

db<>fiddle here