Statistical date Comparison:
1, the same day comparison (To_days (o.order_date) = to_days (#{before_today)) 2, the date parameter is greater than this date of the week Monday statistics (o.order_date & Gt;= date_sub (#{before_today},interval weekday (#{before_today}) + 0 day) ) and ( to_days (#{before_today}) >= to_days (o.order_date) ) 3, The date parameter is greater than this date in the month 1th statistics (O.order_date >= concat (Date_format (#{before_today}), '%y-%m-'), ' 01 ) and ( to_days (#{before_today}) >= to_days (o.order_date) ) 4, the date parameter is greater than this date when the quarterly statistics (O.order_date >= concat (Date_format (Last_day (Makedate (EXTRACT (year from #{before_today}), 1) + interval quarter (#{before_today}) *3-3 MONTH), '%Y-%m- ') and ( to_days (#{before_today}) >= to_days (o.order_date) ) 5, the date parameter is greater than this date half-yearly statistics (to_days (o.order_date) >= to_days (Date_format (#{half_year_date}, '%Y-% m-%d %h:%i:%s ')) ) 6, the date parameter is greater than the full-year statistics for this date (O.order_date = year (#{before_today}))
The first day of the current week: select date_sub (Curdate (), Interval weekday (Curdate ()) + 1 day) The last day of the current week: select date_sub (Curdate (), Interval weekday (Curdate ()) - 5 day) First day of the previous week: select date_sub (Curdate (), Interval weekday (Curdate ()) + 8 day) Last day of the previous week: select date_sub (Curdate (), Interval weekday (Curdate ()) + 2 day first two week day: select date_sub (Curdate (), Interval weekday (Curdate ()) + 15 Day) Last days of the first two week: select date_sub (Curdate (), Interval weekday (Curdate ()) + 9 day) The first day of the current month: select concat (Date_format (now (), '%y-%m-'), ' last_day ') Last day of Month: select last_day (now ()) first day of previous month: select concat (Date_format (Last_day ( Now () - interval 1 month), '%y-%m-') last day of previous month: select last_day (now () - interval 1 month) First day of the previous two month: select concat (Date_format (Last_day (now) - interval 2 month), '%y-%m-') Last day of the first two month: select last_day (now () - interval 2 month) The first day of the current quarter: select concat (Date_format (Last_day (Makedate (EXTRACT) (Year from curdate ()), 1) + interval quarter (Curdate ()) *3-3 month), '%y-%m-') The last day of the current quarter: select last_day (Makedate (EXTRACT (Year from curdate ()), 1) + Interval quarter (Curdate ()) *3-1 month) first day of the previous QUARTER: select concat (Date_format (LAST_ Day (Makedate (EXTRACT (Year from curdate ()), 1) + interval quarter (Curdate ()) *3-6 month), '%y-%m-') Last day of the previous quarter: select last_day (Makedate (EXTRACT (year from Curdate ()), 1) + interval quarter (Curdate ()) *3-4 month) first two QUARTER day: select concat (Date_format (Last_day (MAKedate (EXTRACT (Year from curdate ()), 1) + interval quarter (Curdate ()) *3-9 month), '%y-%m-') Last day of the first two quarter: select last_day (Makedate (EXTRACT ()) , 1) + interval quarter (Curdate ()) *3-7 month)
This article from "Ertan to win" blog, declined reprint!
MySQL Date usage