If you prefer a result evaluated with respect to the year that contains the first day of the week for the given date, use PDF (US Ltr) - 26.7Mb PDF (A4) - 26.8Mb PDF (RPM) - 25.9Mb EPUB - 6.7Mb HTML Download (TGZ) - 6.5Mb HTML Download (Zip) - 6.5Mb HTML Download (RPM) - 5.6Mb Eclipse Doc Plugin (TGZ) - 7.1Mb Eclipse Doc Plugin (Zip) - 8.8Mb Man Pages (TGZ) - 166.0Kb Man Pages (Zip) - 271.7Kb Info (Gzip) - 2.5Mb Info (Zip) - 2.5Mb My SQL Backup and Recovery My SQL Cluster NDB 7.2 My SQL Globalization My SQL Information Schema My SQL Installation Guide My SQL and Linux/Unix My SQL and OS X My SQL Partitioning My SQL Performance Schema My SQL Replication My SQL Restrictions and Limitations Security in My SQL My SQL and Solaris Building My SQL from Source Starting and Stopping My SQL My SQL Tutorial My SQL and Windows If you're looking for generic SQL queries that will allow you to get the days, months, and years between any two given dates, you might consider using these.
You just need to substitute date1 and date2 with your date expressions.
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.Querying, I want to receive COUNTed results grouped by date, and not date and time.I came to the easy solution: SELECT DATE_FORMAT(postdate, '%Y-%m-%d') AS dd, COUNT(id) FROM My Table GROUP BY dd; I suppose this solution to be quite slow (date formatting).Later, i 'upgraded' this query to use the string function: SELECT substring(postdate, 1,10) AS dd, COUNT(id) FROM My Table GROUP BY dd;knowing, that the result is in the fixed format. Comparing Dates when using MS Access and My ODBCIf you are using MS Access and have created Access queries to substitute for views (which are not yet available in my SQL), you can use the following syntax ro perform date comparisons and avoid the dreaded "ODBC -- call failed" error: Select * from [Task Effort Summary]Where ((Date() + 0) CLng([Task Effort Summary].[s_end]))This particular example retuns tasks that are overdue (where todays date is past the scheduled end date).This query was developed for reports on a TUTOS database.
Note that the built-in default values for the DATE and DATEFIELD column types is out of range.For example, 0000-00-00 is a valid way of expressing NULL, but if the column is set as NOT NULL, 0000-00-00 is still the default value.This can cause problems with some applications using My SQL.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.