Menu
How do I specify to mySQL's MONTH() function to return '08' instead of 8 in this query?
I'd like the sort to work datewise. Currently getting results for date like
2006-9 2007-1 2007-10 2007-11 current query:
SELECT COUNT(*), CONCAT(YEAR(`datetime_added`), '-', MONTH(`datetime_added`)) as date FROM `person` WHERE (email = '' OR email IS NULL) GROUP BY date ORDER BY date ASC 
660
SQL Server doesn't provide leading zeroes in a month selection, so you'll have to add them yourself. The easiest way to do this is probably to create a two- or three-digit string and taking a RIGHT() substring to select the rightmost two digits.
The MYSQL MONTH() function is used to retrieve and return the MONTH of the given date or, date time expression. This function returns a numerical value ranging from 1 to 12 representing the month (January to December).
Use the MONTH() function to retrieve a month from a date/datetime/timestamp column in MySQL. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a date/datetime/timestamp column. (In our example, we use the start_date column of date data type).
Use the following instead:
DATE_FORMAT(`datetime_added`,'%Y-%m') Explanation:
DATE_FORMAT() function lets you format the date anyway you like using specifiers described in the table below (taken verbatim from documentation). So a format string '%Y-%m' means: "A full year (4 digits), followed by a dash (-), followed by a two-digit month number".
Note that you can specify the language used for day/month names by setting lc_time_names system variable. Extremely useful. Refer to documentation for more details.
Specifier Description %a Abbreviated weekday name (Sun..Sat) %b Abbreviated month name (Jan..Dec) %c Month, numeric (0..12) %D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) %d Day of the month, numeric (00..31) %e Day of the month, numeric (0..31) %f Microseconds (000000..999999) %H Hour (00..23) %h Hour (01..12) %I Hour (01..12) %i Minutes, numeric (00..59) %j Day of year (001..366) %k Hour (0..23) %l Hour (1..12) %M Month name (January..December) %m Month, numeric (00..12) %p AM or PM %r Time, 12-hour (hh:mm:ss followed by AM or PM) %S Seconds (00..59) %s Seconds (00..59) %T Time, 24-hour (hh:mm:ss) %U Week (00..53), where Sunday is the first day of the week %u Week (00..53), where Monday is the first day of the week %V Week (01..53), where Sunday is the first day of the week; used with %X %v Week (01..53), where Monday is the first day of the week; used with %x %W Weekday name (Sunday..Saturday) %w Day of the week (0=Sunday..6=Saturday) %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v %Y Year, numeric, four digits %y Year, numeric (two digits) %% A literal “%” character %x x, for any “x” not listed above If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
