CODEWITHSUNDEEP
mysql
user3112031
user3112031

Reputation: 39

Select records from previous half year in mysql

I need to get the first and last day of the previous halfyear. So when today is 2020-09-09 I need to get the dates 2020-01-01 and 2020-06-30. I can do this for previous quarter like this:

first day:

SELECT MAKEDATE(YEAR(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)), 1) + INTERVAL QUARTER(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) QUARTER - INTERVAL 1 QUARTER

last day:

SELECT MAKEDATE(YEAR(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)), 1) + INTERVAL QUARTER(DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) QUARTER - INTERVAL 1 DAY

but I'm struggling with halfyear.

Upvotes: 0

Views: 283

Answers (1)

ysth
ysth

Reputation: 98398

I find makedate a pain and would just do:

date( if( month(curdate()) > 6, concat(year(curdate()),'-01-01'), concat(year(curdate())-1,'-07-01') ) )

and

date( if( month(curdate()) > 6, concat(year(curdate()),'-06-30'), concat(year(curdate())-1,'-12-31') ) )

Upvotes: 1

Related Questions