How to get the last day of the month using only month and year?

Question

I want to find out how to find the last day of the month if I only have a year and a month (and no day).

I tried using EOMONTH(), but this function needs the date consisted of year, month and day. I can only use year and month values.

How do I do something like this?

Answer 1

If you are using Sql Server 2012 then I'd use DATEFROMPARTS.

DECLARE @year SMALLINT = 2016
    ,@month TINYINT= 02

SELECT EOMONTH(DATEFROMPARTS(@year,@month,1))

Answer 2

You can still use EOMONTH even if you do not have a day of the month, just use the first of the month as the day of month is not significant in the input.

-- incoming parameters (assuming string but could be int and you could cast them)
DECLARE @month VARCHAR(2) = '11', @year VARCHAR(4) = '2016'

DECLARE @date DATETIME
DECLARE @lastDayOfMonth INT

SELECT @date = CONVERT(date, @year + @month + '01', 101) -- this first part is what you know (year + month), the 01 is just the first day of whatever month appended so the date is valid
-- get the last day of month as a date using EOMONTH and then use DATEPART to get the day of the month
SELECT @lastDayOfMonth = DATEPART(dd, EOMONTH(@date))

SELECT @lastDayOfMonth -- your output on the screen