Get the last day of the month in SQL
SqlSql ServerTsqlSql Problem Overview
I need to get the last day of the month given as a date in SQL. If I have the first day of the month, I can do something like this:
DATEADD(DAY, DATEADD(MONTH,'2009-05-01',1), -1)
But does anyone know how to generalize it so I can find the last day of the month for any given date?
Sql Solutions
Solution 1 - Sql
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)
Solution 2 - Sql
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)
Solution 3 - Sql
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)))
Solution 4 - Sql
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
Solution 5 - Sql
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!!!!
Solution 6 - Sql
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
Solution 7 - Sql
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
Solution 8 - Sql
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)))
Solution 9 - Sql
This works for me, using Microsoft SQL Server 2005:
DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,'2009-05-01')+1,0))
Solution 10 - Sql
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())
Solution 11 - Sql
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)
Solution 12 - Sql
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()))
Solution 13 - Sql
This query can also be used.
DECLARE @SelectedDate DATE = GETDATE()
SELECT DATEADD(DAY, - DAY(@SelectedDate), DATEADD(MONTH, 1 , @SelectedDate)) EndOfMonth
Solution 14 - Sql
My 2 cents:
select DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(day,(0-(DATEPART(dd,'2008-02-12')-1)),'2008-02-12')))
Raj
Solution 15 - Sql
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.
Solution 16 - Sql
Using SQL Server, here is another way to find last day of month :
SELECT DATEADD(MONTH,1,GETDATE())- day(DATEADD(MONTH,1,GETDATE()))
Solution 17 - Sql
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');
Solution 18 - Sql
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())
Solution 19 - Sql
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: https://stackoverflow.com/questions/1520789/how-can-i-select-the-first-day-of-a-month-in-sql
Solution 20 - 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')
Solution 21 - Sql
---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