CODEWITHSUNDEEP
.netsql-serversql-server-2012
Gans
Gans

Reputation: 171

How find last day of previous month on the basis of Month and year in SQL Server

I have a requirement where I have to find the closing balance of previous month on the basis of month and year.

I have tried this code:

DECLARE @month NUMERIC = 11
DECLARE @Year NUMERIC = 2017

SELECT  
    DATEADD(DAY, -1, DATEADD(MONTH, @Month, DATEADD(YEAR, @Year - 1900, 0)))

but this is returning the value of last day of the current month '2017-11-30 00:00:00.000'.

Instead of this I want the last day of the previous month: '2017-10-31 00:00:00.000'

Upvotes: 0

Views: 48

Answers (2)

Squirrel
Squirrel

Reputation: 24763

or you can simplify a bit by only using 2 DATEADD()

SELECT  DATEADD(month, @Month - 1, DATEADD(year, @Year - 1900, -1))

or convert the @Year to months and only use single DATEADD()

SELECT  DATEADD(month, (@Year - 1900) * 12 + @Month - 1, - 1)

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You are going right just add -1 to your variable @Month for last month date:

SELECT  Dateadd(day, -1, Dateadd(month, @Month-1, 
                                      Dateadd(year, @Year - 1900, 0) 
                                             ))

Result :

2017-10-31 00:00:00.000

Upvotes: 1

Related Questions