Post your trick

4 Responses to “Getting first day of the month in mysql”

• 
  Mazda May 5th, 2011 at 6:32 pm

  Hi, I’m totally new to mysql and was looking for exactly this… however, your solution returns the time of day as well… for example, running it right now returns 5/1/2011 2:30PM.

  to get the exact first of the month, wrap the whole thing in DATE() function…

  
  
• 
  Richard July 7th, 2011 at 11:56 pm

  The code presented is definitely a trick.
  It will trick you on the the first day of March in any year.

  For example, DATE_ADD(LAST_DAY(DATE_SUB(’2011-03-01′, interval 30 day)), interval 1 day)
  returns ‘2011-02-01′ instead of ‘2011-03-01′

  There are various ways to produce the desired result.
  Here is one:

  SELECT DATE_FORMAT(NOW() ,’%Y-%m-01′)

  ps To MAZDA, The function DATE_SUB returns the time if you use a TIMESTAMP field, instead of a DATE field, as the parameter.

  
  
• 
  ester October 24th, 2011 at 2:23 pm

  how to find out first week of Wednesday in current month using date. Please help me.

  
  
• 
  virtualkev December 7th, 2011 at 10:19 am

  I figured this would be most elegant.

  CREATE FUNCTION FIRST_DAY (s CHAR(10))
  RETURNS CHAR(10) DETERMINISTIC
  RETURN concat(substr(s,’1′,’8′),’01′);

  mysql> select first_day(curdate());
  +———————-+
  | first_day(curdate()) |
  +———————-+
  | 2011-12-01 |
  +———————-+
  1 row in set (0.00 sec)

  mysql> select first_day(date_sub(curdate(), interval 1 month));
  +————————————————–+
  | first_day(date_sub(curdate(), interval 1 month)) |
  +————————————————–+
  | 2011-11-01 |
  +————————————————–+
  1 row in set (0.00 sec)

  mysql> select first_day(’1980-12-25′);
  +————————-+
  | first_day(’1980-12-25′) |
  +————————-+
  | 1980-12-01 |
  +————————-+
  1 row in set (0.00 sec)