Artful Common Queries

YearMonth()

We often need to compute datetimes based on year and month. This tiny function simplifies such queries:

set global log_bin_trust_function_creators=1;
create function yearmonth(d date) returns int
return 100*year(d)+month(d);

Then to find date values within the three-month period bounded by the first day of last month and the last day of next month, write ...

select d
from tbl
where yearmonth(d) between yearmonth(curdate()-interval 1 month) and yearmonth(curdate()+interval 1 month);

Last updated 23 Aug 2024