Thursday, December 13, 2007

First Day of the Month

I was looking for a function in the MySQL documentation on how to get the first day of the month and I couldn't find one. Although I found that there was a LAST_DAY function. So, here's my function to get the first day of the month.

select DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 30 day), interval 1 day);

This is how I computed the first day of the month. I'm not sure if there is a simpler way of doing this.

18 comments:

Anonymous said...

Hi This is good but it can easily done by this statement:
SELECT DATE_SUB(curdate(),INTERVAL (DAY(curdate())-1) DAY)

Anonymous said...

Hello

Your function doesnt work properly:

select DATE_ADD(LAST_DAY(DATE_SUB('2009-03-01', interval 30 day)), interval 1 day)

returns "2009-02-01" instead "2009-03-01"

I think the anonymous's statement is ok.

Anonymous said...

just change the day of the month part=1

Unknown said...

Hi this statement below is even easier :-

SELECT DATE_FORMAT(NOW() ,'%Y-%m-01');

Rajesh said...

Hi,
Can you please help me in getting the first date of the year in MYSQL. iN SQL SERVER
SELECT '01/01/' + convert(varchar(10),YEAR(getdate()))

But i m finding it difficuklty to find in MYSQL .
My o/p must be :01/01/2009/
Help me in finding a soln?

Anonymous said...

The original posting would still work if we make a small change; instead of subtracting 30 days, we would change it to subtracting 1 month instead. So,

select DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 1 month), interval 1 day);

or

select DATE_ADD(LAST_DAY(DATE_SUB('2009-03-01', interval 1 month), interval 1 day) --> '2009-03-01'

Anonymous said...

Quote:

"Hi this statement below is even easier :-

SELECT DATE_FORMAT(NOW() ,'%Y-%m-01'); "

This looks simpler however it returns a string and not a DATETIME value.

Try this:

CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE);

or

CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATETIME);

Anonymous said...

Thankssss!!! You´ve saved my life!!

Anonymous said...

SELECT DATE_FORMAT(curdate(), '%Y%m01')
-> 20100901

musfiq said...
This comment has been removed by the author.
musfiq said...

SELECT cast( concat( YEAR( DATE_ADD( CURRENT_DATE( ) , INTERVAL 1
MONTH ) ) , '-', MONTH( DATE_ADD( CURRENT_DATE( ) , INTERVAL 1
MONTH ) ) , '-01' ) AS DATE ) AS firstDateNextMonth

Woody said...

Useful post (and comments) - I use CONVERT_TZ to also get a unix timestamp for this too - check out http://blog.woodylabs.com/2011/11/mysql-datetime-examples-firstlast-day-of-the-year-dates-and-unix-times/

virtualkev said...

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)

Jack said...

DATE_ADD(LAST_DAY(DATE_ADD(LAST_DAY(MD.date_value), INTERVAL -40 DAY)), INTERVAL 1 DAY)

Anonymous said...

This will always give you this first day of the month, with 00:00:00 for the time:

SELECT LAST_DAY(NOW()) - INTERVAL 1 MONTH + INTERVAL 1 DAY

els said...

TRUNC(SYSDATE,'MM')

Anonymous said...

Wasn't pretty easier to just use 01 or 1 as the first day of the month.
In the world I live I don't know any calendar where the first day isn't 1... kkkkkkkkkkk

Anonymous said...

select date_add(last_day(date_add(current_date, INTERVAL -1 Month)), INTERVAL 1 DAY)