Number of days between date1 and date2: TO_DAYS(date2) - TO_DAYS(date1)Number of months between date1 and date2: IF((((YEAR(date2) - 1) * 12 + MONTH(date2)) - ((YEAR(date1) - 1) * 12 + MONTH(date1))) Spent some time trying to work out how to calculate the month start x months ago ( so that I can create historical stats on the fly)here is what I came up with..((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-6)*100)+1)this gives you the first day of the month six months before the start of the current month in datetime format Several times i have come to a followng date/time problem: In the table i am storing both date and time information in the datetime column.
You just need to substitute date1 and date2 with your date expressions.
NOTE: Some of these formulas are complex because they account for all cases where date1 date2.
Additionally, these formulas can be used in very generic queries where aliases and temporary variables are not allowed.
This section describes the functions that can be used to manipulate temporal values.
See Section 11.3, “Date and Time Types”, for a description of the range of values each date and time type has and the valid formats in which values may be specified.
The query also selects rows with dates that lie in the future.Functions that expect date values usually accept datetime values and ignore the time part.Functions that expect time values usually accept datetime values and ignore the date part.Functions that return the current date or time each are evaluated only once per query at the start of query execution.This means that multiple references to a function such as within a single query always produce the same result.(For our purposes, a single query also includes a call to a stored program (stored routine, trigger, or event) and all subprograms called by that program.) This principle also applies to function reliable when combined with other functions that extract a date part from a date.