CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
gene
gene

Reputation: 2108

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

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?

Upvotes: 6

Views: 10924

Answers (3)

Anthony Hancock
Anthony Hancock

Reputation: 931

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))

Upvotes: 14

Igor
Igor

Reputation: 62213

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

Upvotes: 2

S3S
S3S

Reputation: 25112

VARCHAR TYPES

DECLARE @D DATE
DECLARE @YearV VARCHAR(4) = '2016'
DECLARE @MonthV VARCHAR(2) = '12'

SET @D = DATEADD(dd,-1,DATEADD(mm,1,CAST(@YearV + @MonthV + '01' AS DATE)))

SELECT @D

INT TYPES

DECLARE @D DATE
DECLARE @Year INT = '2016'
DECLARE @Month INT = '11'

SET @D = DATEADD(dd,-1,DATEADD(mm,1,CAST(CAST(@Year AS VARCHAR(4)) + CAST(@Month AS VARCHAR(2)) + '01' AS DATE)))

SELECT @D

COMBINED TYPES

some SUBSTRING code depending on the format... :)

Upvotes: 1

Related Questions