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.
Subscribe to:
Post Comments (Atom)
18 comments:
Hi This is good but it can easily done by this statement:
SELECT DATE_SUB(curdate(),INTERVAL (DAY(curdate())-1) DAY)
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.
just change the day of the month part=1
Hi this statement below is even easier :-
SELECT DATE_FORMAT(NOW() ,'%Y-%m-01');
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?
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'
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);
Thankssss!!! You´ve saved my life!!
SELECT DATE_FORMAT(curdate(), '%Y%m01')
-> 20100901
SELECT cast( concat( YEAR( DATE_ADD( CURRENT_DATE( ) , INTERVAL 1
MONTH ) ) , '-', MONTH( DATE_ADD( CURRENT_DATE( ) , INTERVAL 1
MONTH ) ) , '-01' ) AS DATE ) AS firstDateNextMonth
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/
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)
DATE_ADD(LAST_DAY(DATE_ADD(LAST_DAY(MD.date_value), INTERVAL -40 DAY)), INTERVAL 1 DAY)
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
TRUNC(SYSDATE,'MM')
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
select date_add(last_day(date_add(current_date, INTERVAL -1 Month)), INTERVAL 1 DAY)
Post a Comment