From SQL Server 2012 you can use the EOMONTH function.

Returns the last day of the month that contains the specified date,
with an optional offset.

Syntax

EOMONTH ( start_date [, month_to_add ] ) 

How … I can find the last day of the month for any given date?

SELECT EOMONTH(@SomeGivenDate)

Here’s my version. No string manipulation or casting required, just one call each to the DATEADD, YEAR and MONTH functions:

DECLARE @test DATETIME
SET @test = GETDATE()  -- or any other date

SELECT DATEADD(month, ((YEAR(@test) - 1900) * 12) + MONTH(@test), -1)

This works by taking the number of months that have passed since the initial date in SQL’s date system (1/1/1990), then adding that number of months to the date “-1” (12/31/1899) using the DATEADD function, which returns a proper datetime at the corresponding month’s end (that of the date you specified). It relies on the fact that December always ends on the 31st and there are no months longer than that.

You could get the days in the date by using the DAY() function:

dateadd(day, -1, dateadd(month, 1, dateadd(day, 1 - day(date), date)))

Works in SQL server

Declare @GivenDate datetime
SET @GivenDate = GETDATE()

Select DATEADD(MM,DATEDIFF(MM, 0, @GivenDate),0) --First day of the month 

Select DATEADD(MM,DATEDIFF(MM, -1, @GivenDate),-1) --Last day of the month

For SQL server 2012 or above use EOMONTH to get the last date of month

SQL query to display end date of current month

DECLARE @currentDate DATE = GETDATE()
SELECT EOMONTH (@currentDate) AS CurrentMonthED

SQL query to display end date of Next month

DECLARE @currentDate DATE = GETDATE()
SELECT EOMONTH (@currentDate, 1 ) AS NextMonthED

I know this is a old question but here is another solution that works for me

SET @dtDate = "your date"
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

And if some one is looking for different examples here is a link http://blog.sqlauthority.com/2007/08/18/sql-server-find-last-day-of-any-month-current-previous-next/

I hope this helps some one else.
stackoverflow Rocks!!!!

Based on the statements:

SELECT DATEADD(MONTH, 1, @x)           -- Add a month to the supplied date @x

and

SELECT DATEADD(DAY,  0 - DAY(@x), @x)  -- Get last day of month previous to the supplied date @x

how about adding a month to date @x and then retrieving the last day of the month previous to that (i.e. The last day of the month of the supplied date)

DECLARE @x  DATE = '20-Feb-2012' 
SELECT DAY(DATEADD(DAY,  0 - DAY(DATEADD(MONTH, 1, @x)), DATEADD(MONTH, 1, @x)))

Note: This was test using SQL Server 2008 R2

Just extend your formula out a little bit:

dateadd(day, -1,
    dateadd(month, 1,
        cast(month('5/15/2009') as varchar(2)) + 
        '/1/' + 
        cast(year('5/15/2009') as varchar(4)))

This works for me, using Microsoft SQL Server 2005:

DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,'2009-05-01')+1,0))

WinSQL to get last day of last month (i.e today is 2017-02-09, returns 2017-01-31:
Select dateadd(day,-day(today()),today())

Try to run the following query, it will give you everything you want 🙂

Declare @a date =dateadd(mm, Datediff(mm,0,getdate()),0)
Print('First day of Current Month:')
Print(@a)
Print('')
set @a = dateadd(mm, Datediff(mm,0,getdate())+1,-1)
Print('Last day of Current Month:')
Print(@a)
Print('')

Print('First day of Last Month:')
set @a = dateadd(mm, Datediff(mm,0,getdate())-1,0)
Print(@a)
Print('')

Print('Last day of Last Month:') 
set @a = dateadd(mm, Datediff(mm,0,getdate()),-1)
Print(@a)
Print('')


Print('First day of Current Week:')
set @a = dateadd(ww, Datediff(ww,0,getdate()),0)
Print(@a)
Print('')

Print('Last day of Current Week:')
set @a = dateadd(ww, Datediff(ww,0,getdate())+1,-1)
Print(@a)
Print('')

Print('First day of Last Week:')
set @a =  dateadd(ww, Datediff(ww,0,getdate())-1,0)
Print(@a)
Print('')

Print('Last day of Last Week:')
set @a =  dateadd(ww, Datediff(ww,0,getdate()),-1)
Print(@a)

WinSQL: I wanted to return all records for last month:

where DATE01 between dateadd(month,-1,dateadd(day,1,dateadd(day,-day(today()),today()))) and dateadd(day,-day(today()),today())

This does the same thing:

where month(DATE01) = month(dateadd(month,-1,today())) and year(DATE01) = year(dateadd(month,-1,today()))

This query can also be used.

DECLARE @SelectedDate DATE =  GETDATE()

SELECT DATEADD(DAY, - DAY(@SelectedDate), DATEADD(MONTH, 1 , @SelectedDate)) EndOfMonth

My 2 cents:

select DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(day,(0-(DATEPART(dd,'2008-02-12')-1)),'2008-02-12')))

Raj

using sql server 2005, this works for me:

select dateadd(dd,-1,dateadd(mm,datediff(mm,0,YOUR_DATE)+1,0))

Basically, you get the number of months from the beginning of (SQL Server) time for YOUR_DATE. Then add one to it to get the sequence number of the next month. Then you add this number of months to 0 to get a date that is the first day of the next month. From this you then subtract a day to get to the last day of YOUR_DATE.

Take some base date which is the 31st of some month e.g. ‘20011231’. Then use the
following procedure (I have given 3 identical examples below, only the @dt value differs).

declare @dt datetime;

set @dt = '20140312'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');



set @dt = '20140208'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');



set @dt = '20140405'

SELECT DATEADD(month, DATEDIFF(month, '20011231', @dt), '20011231');

Using SQL Server, here is another way to find last day of month :

SELECT DATEADD(MONTH,1,GETDATE())- day(DATEADD(MONTH,1,GETDATE()))

I wrote following function, it works.

It returns datetime data type. Zero hour, minute, second, miliseconds.

CREATE Function [dbo].[fn_GetLastDate]
(
    @date datetime
)
returns datetime
as
begin

declare @result datetime

 select @result = CHOOSE(month(@date),  
 DATEADD(DAY, 31 -day(@date), @date),
 IIF(YEAR(@date) % 4 = 0, DATEADD(DAY, 29 -day(@date), @date), DATEADD(DAY, 28 -day(@date), @date)), 
 DATEADD(DAY, 31 -day(@date), @date) ,
 DATEADD(DAY, 30 -day(@date), @date), 
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 30 -day(@date), @date), 
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 30 -day(@date), @date),
 DATEADD(DAY, 31 -day(@date), @date),
 DATEADD(DAY, 30 -day(@date), @date), 
 DATEADD(DAY, 31 -day(@date), @date))

 return convert(date, @result)

end

It’s very easy to use.
2 example:

select [dbo].[fn_GetLastDate]('2016-02-03 12:34:12')

select [dbo].[fn_GetLastDate](GETDATE())

Based on the most voted answer at below link I came up with the following solution:

 declare  @mydate date= '2020-11-09';
  SELECT DATEADD(month, DATEDIFF(month, 0, @mydate)+1, -1) AS lastOfMonth

link: How can I select the first day of a month in SQL?

I couldn’t find an answer that worked in regular SQL, so I brute forced an answer:

SELECT *
FROM orders o
WHERE (MONTH(o.OrderDate) IN ('01','03','05','07','08','10','12') AND DAY(o.OrderDate) = '31')
    OR (MONTH(o.OrderDate) IN ('04','06','09','11') AND DAY(o.OrderDate) = '30')
    OR (MONTH(o.OrderDate) IN ('02') AND DAY(o.OrderDate) = '28')

–## Useful Date Functions

SELECT

GETDATE() AS [DateTime],

CAST(GETDATE() AS DATE) AS [Date],

DAY(GETDATE()) AS [Day of Month],

FORMAT(GETDATE(),'MMMM') AS [Month Name],

FORMAT(GETDATE(),'MMM') AS [Month Short Name],

FORMAT(GETDATE(),'MM') AS [Month No],

YEAR(GETDATE()) AS [Year],

CAST(DATEADD(DD,-(DAY(GETDATE())-1),GETDATE()) AS DATE) AS [Month Start Date],

EOMONTH(GETDATE()) AS [Month End Date],

CAST(DATEADD(M,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE()),0)) AS DATE) AS [Previous Month Start Date],

CAST(DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE()),0)) AS DATE) AS [Previous Month End Date],

CAST(DATEADD(M,+1,DATEADD(MM, DATEDIFF(M,0,GETDATE()),0)) AS DATE) AS [Next Month Start Date],

CAST(DATEADD(D,-1,DATEADD(MM, DATEDIFF(M,0,GETDATE())+2,0)) AS DATE) AS [Next Month End Date],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE()),0) AS DATE) AS [First Day of Current Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())+1,-1) AS DATE) AS [Last Day of Current Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())-1,0) AS DATE) AS [First Day of Last Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE()),-1) AS DATE) AS [Last Day of Last Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())+1,0) AS DATE) AS [First Day of Next Week],

CAST(DATEADD(WW, DATEDIFF(WW,0,GETDATE())+2,-1) AS DATE) AS [Last Day of Next Week]

user a function EOMONTH(GETDATE())

SELECT EOMONTH(GETDATE())

---Start/End of previous Month 
Declare @StartDate datetime, @EndDate datetime

set @StartDate = DATEADD(month, DATEDIFF(month, 0, GETDATE())-1,0) 
set @EndDate = EOMONTH (DATEADD(month, DATEDIFF(month, 0, GETDATE())-1,0)) 

SELECT @StartDate,@EndDate