Get First & Last Day of Month from Year & Month variables

Question

Using MySql

I have the following variables (filled in with "3" and "2014" as an example):

SET @Month = 3;
SET @Year = 2014;

Desired Results

I'd like to get the first day of the month and the last day of the month separately, 
using the variables (avoiding any NOW() or CURDATE() formulas), in %Y-%m-%d format. In other words:

2014-03-01
2014-03-31

I've tried many possibilities of the following base functions, plus some I've deleted

SELECT LAST_DAY(`SELECT(@Year)-03-01`)
SELECT LAST_DAY('@Year-@Month')
SELECT DATE_FORMAT(?, '%Y-%m-01')

`SELECT STR_TO_DATE(CONCAT(@Year,'-',@Month,'-01'),'%Y-%m-%d'), LAST_DAY(STR_TO_DATE(CONCAT(@Year,'-',@Month,'-01'),'%Y-%m-%d'));` — Wrikken, Mar 21 '14 at 19:54
is that you are looking for [**fiddle**](http://sqlfiddle.com/#!2/d41d8/34043) — M Khalid Junaid, Mar 21 '14 at 20:13

Slowcoder · Accepted Answer · 2014-03-21T20:21:49.457

8

SET @Months = 3;
SET @Years = 2014;
SELECT DATE_FORMAT(DATE(concat(@Years, '-', @Months, '-01')), '%Y-%m-%d');
SELECT LAST_DAY(DATE(concat(@Years, '-', @Months, '-01')));

'Year' and 'Month' are reserved function names in MySQL. So I would suggest renaming it to avoid the confusion.

edited Mar 21 '14 at 20:21

answered Mar 21 '14 at 20:16

Slowcoder

2,060
3
16
21

score 0 · Answer 2 · edited May 23 '17 at 12:25

0

SELECT DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)), 
                INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL 1 MONTH)))-1 DAY) AS firstOfNextMonth,
       LAST_DAY(DATE_ADD(NOW(), 
                INTERVAL 1 MONTH)) AS lastOfNextMonth

source: Get the first and last date of next month in MySQL

edited May 23 '17 at 12:25

Community

1
1

answered Mar 21 '14 at 19:51

suuuzi

602
1
8
19

This doesn't include the variables – Ken Mar 21 '14 at 20:00
i just post an example so you can adapt :P – suuuzi Mar 21 '14 at 20:02
1

Thanks, but that's what I need help with :) Integrating the variables – Ken Mar 21 '14 at 20:02
1

oh, ok. sorry i cant help :( – suuuzi Mar 21 '14 at 20:05

Get First & Last Day of Month from Year & Month variables

2 Answers2