SQL variables month to covert date

Question

When adding variables to choose the month and year to query

@Month = 5 @Year = 2017

I want to show two columns start_day and last_day of the @month and @year

startdaymonth       lastdaymonth
=============       ============
2017-05-01           2017-05-31

Possible duplicate of [Get the first and last date of next month in MySQL](https://stackoverflow.com/questions/3009896/get-the-first-and-last-date-of-next-month-in-mysql) — Martin Schneider, Dec 20 '17 at 08:01
Note that sql is for the storage and retrieval of data. It's not intended for random programming tasks. — Strawberry, Dec 20 '17 at 08:41

score 0 · Accepted Answer · edited Dec 20 '17 at 07:21

0

Try this:

DECLARE @Month INT = 5 
DECLARE @Year INT = 2017



SELECT DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) as FirstDate,
       DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) AS LastDate

edited Dec 20 '17 at 07:21

Rob

26,989
16
82
98

answered Dec 20 '17 at 06:52

Aswani Madhavan

816
6
19

Could you please elaborate, *why* and *how* this solves the OPs issue? – Paul Kertscher Dec 20 '17 at 07:16

Damini Suthar · Answer 2 · 2017-12-20T10:59:07.567

0

DECLARE @Month int
DECLARE @Year int

set @Month = 5
set @Year = 2017

select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) as startdaymonth // To get the first day of the month

select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0)))  as lastdaymonth  // To get the last day of the month

edited Dec 20 '17 at 10:59

answered Dec 20 '17 at 06:52

Damini Suthar

1,470
2
14
43

When you post any code as an answer, please also try to put some explanation of what that code does. It will help anyone understand your answer. – Rahul Gupta Dec 20 '17 at 07:51
Sure, added here. – Damini Suthar Dec 20 '17 at 11:00

score 0 · Answer 3 · answered Dec 20 '17 at 06:53

0

IF, you are working with SQL Server use eomonth() function for last day of specified month

declare @Month int = 2,  @Year int = 2018

select cast(concat(@month,'/',1,'/',@year) as date), eomonth(concat(@month,'/',1,'/',@year))

answered Dec 20 '17 at 06:53

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 4 · answered Dec 20 '17 at 07:30

Try this:

;with cte as (
select cast('2017-01-01' as date) as [FirstOfMonth]
union all
select dateadd(month, 1, FirstOfMonth) from cte
where datepart(year, FirstOfMonth) < 2018
)

select FirstOfMonth,
       dateadd(day, -1, LEAD(FirstOfMonth, 1) over (order by FirstOfMonth))
from cte

First part gets you first days of all months in 2017 plus first of Jan. of 2018. Second part gets the day before, i.e. last day of the month.

Try this and you'll get better understanding :) Only thing is, that you have to exclude last record, which is 2018-01-01 :)

SQL variables month to covert date

4 Answers4