0

How to get last date of particular month from the month ID and year in SQL ?

Inputs : 

@MonthID INT = 2,
@Year    INT = 2015

Required output : 28/02/2015

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mia
  • 5
  • 1
  • 4
  • 2
    Which **version** of SQL Server? As of SQL Server **2012**, there's an [`EOMONTH`](https://msdn.microsoft.com/en-us/library/hh213020.aspx) function for this exact purpose – marc_s Jan 15 '16 at 10:07
  • Yes its SQL Server 2012 .. Got the answer ..Thank you – Mia Jan 15 '16 at 10:54

3 Answers3

1

It's easy in SQL Server 2012+

To frame date from the year and month use DATEFROMPARTS (Transact-SQL)

To find the last day of month use EOMONTH (Transact-SQL) 

Declare @MonthID INT = 2,
        @Year    INT = 2015

SELECT Eomonth(Datefromparts(@Year, @MonthID, 1))

For any thing less then Sql server 2012

SELECT Dateadd(dd, -1, Dateadd(mm, 1, Cast(Cast(@Year AS CHAR(4)) + '-' 
                       + Cast(@MonthID AS VARCHAR(2))+'-'+'1' AS DATE)))

Note: If the variables are of Varchar type then you can remove the cast to varchar

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
1

You can find the last day of the month in the following manner

Declare @MonthID varchar(10) = '02', @Year varchar(10) = '2015'
select dateadd(dd,-1,dateadd(MM,1,cast(@Year+'-'+@MonthID+'-1' as date)))
T.Yetis
  • 11
  • 2
0

This is how I did it.

DECLARE @MonthID INT = 3,
@Year    INT = 2015

DECLARE @InputDate DATE, @TempDate DATE, @OutputDate DATE

SET @InputDate = CAST(CAST(@Year AS VARCHAR) + '-' + CAST(@MonthID AS varchar) + '-01' AS DATE)
SET @TempDate = @InputDate

WHILE (DATEPART(m, @InputDate) = DATEPART(m, @TempDate))
BEGIN
    SET @OutputDate = @TempDate
    SET @TempDate = DATEADD(d,1,@TempDate)  
END 

SELECT @OutputDate
KMB
  • 473
  • 1
  • 5
  • 15