tag:blogger.com,1999:blog-6470564425716599014.post7173678276197466992..comments2023-09-15T02:01:43.321-07:00Comments on MySQL DBA: First Day of the MonthBichonfrise74http://www.blogger.com/profile/08479429220386210543noreply@blogger.comBlogger18125tag:blogger.com,1999:blog-6470564425716599014.post-11163123187750793232014-06-05T20:41:03.293-07:002014-06-05T20:41:03.293-07:00select date_add(last_day(date_add(current_date, IN...select date_add(last_day(date_add(current_date, INTERVAL -1 Month)), INTERVAL 1 DAY)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-19280582949954137722013-09-09T13:04:51.473-07:002013-09-09T13:04:51.473-07:00Wasn't pretty easier to just use 01 or 1 as th...Wasn't pretty easier to just use 01 or 1 as the first day of the month.<br />In the world I live I don't know any calendar where the first day isn't 1... kkkkkkkkkkkAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-62000146915925736342013-06-05T10:43:07.196-07:002013-06-05T10:43:07.196-07:00TRUNC(SYSDATE,'MM')TRUNC(SYSDATE,'MM') elshttps://www.blogger.com/profile/15248135109749375640noreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-38553280927130915882013-03-27T15:14:39.271-07:002013-03-27T15:14:39.271-07:00This will always give you this first day of the mo...This will always give you this first day of the month, with 00:00:00 for the time:<br /><br />SELECT LAST_DAY(NOW()) - INTERVAL 1 MONTH + INTERVAL 1 DAYAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-26612268420970059182012-04-26T09:00:02.154-07:002012-04-26T09:00:02.154-07:00DATE_ADD(LAST_DAY(DATE_ADD(LAST_DAY(MD.date_value)...DATE_ADD(LAST_DAY(DATE_ADD(LAST_DAY(MD.date_value), INTERVAL -40 DAY)), INTERVAL 1 DAY)Jackhttps://www.blogger.com/profile/12585477782616580869noreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-34994450105770285542011-12-07T01:59:13.410-08:002011-12-07T01:59:13.410-08:00CREATE FUNCTION FIRST_DAY (s CHAR(10))
RETURNS CHA...CREATE FUNCTION FIRST_DAY (s CHAR(10))<br />RETURNS CHAR(10) DETERMINISTIC<br />RETURN concat(substr(s,'1','8'),'01');<br /><br />mysql> select first_day(curdate());<br />+----------------------+<br />| first_day(curdate()) |<br />+----------------------+<br />| 2011-12-01 |<br />+----------------------+<br />1 row in set (0.00 sec)<br /><br />mysql> select first_day(date_sub(curdate(), interval 1 month));<br />+--------------------------------------------------+<br />| first_day(date_sub(curdate(), interval 1 month)) |<br />+--------------------------------------------------+<br />| 2011-11-01 |<br />+--------------------------------------------------+<br />1 row in set (0.00 sec)<br /><br />mysql> select first_day('1980-12-25');<br />+-------------------------+<br />| first_day('1980-12-25') |<br />+-------------------------+<br />| 1980-12-01 |<br />+-------------------------+<br />1 row in set (0.00 sec)virtualkevnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-84612787297930376212011-11-10T02:19:25.597-08:002011-11-10T02:19:25.597-08:00Useful post (and comments) - I use CONVERT_TZ to a...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/Woodyhttp://blog.woodylabs.comnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-9741004667959661722011-08-23T11:01:10.981-07:002011-08-23T11:01:10.981-07:00SELECT cast( concat( YEAR( DATE_ADD( CURRENT_DATE(...SELECT cast( concat( YEAR( DATE_ADD( CURRENT_DATE( ) , INTERVAL 1<br />MONTH ) ) , '-', MONTH( DATE_ADD( CURRENT_DATE( ) , INTERVAL 1<br />MONTH ) ) , '-01' ) AS DATE ) AS firstDateNextMonthmusfiqhttps://www.blogger.com/profile/10044021534017453534noreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-83479419165150555992011-08-23T10:58:57.684-07:002011-08-23T10:58:57.684-07:00This comment has been removed by the author.musfiqhttps://www.blogger.com/profile/10044021534017453534noreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-30297546562341220532010-09-15T11:41:26.992-07:002010-09-15T11:41:26.992-07:00SELECT DATE_FORMAT(curdate(), '%Y%m01')
-&...SELECT DATE_FORMAT(curdate(), '%Y%m01')<br />-> 20100901Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-33742402763938136672010-07-20T07:49:13.207-07:002010-07-20T07:49:13.207-07:00Thankssss!!! You´ve saved my life!!Thankssss!!! You´ve saved my life!!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-34292772782419619882010-06-28T10:54:45.561-07:002010-06-28T10:54:45.561-07:00Quote:
"Hi this statement below is even easi...Quote:<br /><br />"Hi this statement below is even easier :-<br /><br />SELECT DATE_FORMAT(NOW() ,'%Y-%m-01'); "<br /><br />This looks simpler however it returns a string and not a DATETIME value.<br /><br />Try this:<br /><br />CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE);<br /><br />or <br /><br />CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATETIME);Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-15565570333180095242010-01-27T12:17:43.635-08:002010-01-27T12:17:43.635-08:00The original posting would still work if we make a...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,<br /><br />select DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 1 month), interval 1 day);<br /><br />or<br /><br />select DATE_ADD(LAST_DAY(DATE_SUB('2009-03-01', interval 1 month), interval 1 day) --> '2009-03-01'Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-72160484962872004642009-07-16T23:34:30.897-07:002009-07-16T23:34:30.897-07:00Hi,
Can you please help me in getting the first ...Hi,<br /> Can you please help me in getting the first date of the year in MYSQL. iN SQL SERVER <br />SELECT '01/01/' + convert(varchar(10),YEAR(getdate()))<br /><br />But i m finding it difficuklty to find in MYSQL .<br />My o/p must be :01/01/2009/<br />Help me in finding a soln?Rajeshhttps://www.blogger.com/profile/04134703842988563111noreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-27288100567525496882009-06-25T09:56:40.338-07:002009-06-25T09:56:40.338-07:00Hi this statement below is even easier :-
SELECT...Hi this statement below is even easier :- <br /><br />SELECT DATE_FORMAT(NOW() ,'%Y-%m-01');Unknownhttps://www.blogger.com/profile/10715237262693651458noreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-26776756373781022082009-05-21T06:33:00.340-07:002009-05-21T06:33:00.340-07:00just change the day of the month part=1just change the day of the month part=1Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-42537806194098885522009-04-17T05:12:00.000-07:002009-04-17T05:12:00.000-07:00Hello
Your function doesnt work properly:
select...Hello<br /><br />Your function doesnt work properly:<br /><br />select DATE_ADD(LAST_DAY(DATE_SUB('2009-03-01', interval 30 day)), interval 1 day)<br /><br />returns "2009-02-01" instead "2009-03-01"<br /><br />I think the anonymous's statement is ok.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6470564425716599014.post-19832942875626748452009-02-06T02:33:00.000-08:002009-02-06T02:33:00.000-08:00Hi This is good but it can easily done by this sta...Hi This is good but it can easily done by this statement:<BR/>SELECT DATE_SUB(curdate(),INTERVAL (DAY(curdate())-1) DAY)Anonymousnoreply@blogger.com