Get the last day of the month in SQL

Question

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?

Why is this query not general ? – MasterJoe Sep 07 '20 at 19:11 — MasterJoe, Sep 07 '20 at 19:11

Martin Smith · Answer 1 · 2018-01-05T19:38:38.797

91

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)

edited Jan 05 '18 at 19:38

answered Jul 21 '11 at 00:18

Martin Smith

438,706
87
741
845

score 72 · Accepted Answer · edited Jul 03 '23 at 17:11

72

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.

edited Jul 03 '23 at 17:11

volfied

47
7

answered Jun 26 '09 at 22:28

LukeH

263,068
57
365
409

1

I can't understand how this query returns the last day of a month, although it works of course. Could you further explain it a bit please? – Btc Sources Jun 28 '19 at 09:43
2

If you use SQL Server >= 2012, then use EOMONTH (see the answer from Martin Smith) instead – Henrik Høyer Sep 30 '19 at 09:45
1

The solution takes as base the first known date by SQL Server (1900-01-01). Then it calculates the number of months that have passed until your given date (@test) by fours steps: 1. Calculate how many years have passed: given year - 1900 2. Multiply the result of step 1 by 12 to transform it to months 3. Add to result of step 2 the months of given date 4. The magic number is finally converted to DATETIME by using DATEADD function removing one month – TDT Mar 09 '20 at 21:37

score 13 · Answer 3 · edited Jun 27 '09 at 16:58

13

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

edited Jun 27 '09 at 16:58

Eric

92,005
12
114
115

answered Jun 26 '09 at 22:15

jamuraa

3,419
25
29

Msg 8116, Level 16, State 1, Line 1 Argument data type varchar is invalid for argument 2 of dateadd function. – Raj Jun 27 '09 at 00:58
I fixed the syntax for you. DATEADD is `(datepart, distance, date)`. – Eric Jun 27 '09 at 16:58

Sri · Answer 4 · 2018-08-09T15:51:00.327

6

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

edited Aug 09 '18 at 15:51

answered Aug 09 '18 at 15:44

Sri

135
1
6

First date (not day) of a month is always the 01. Why do you need a function to find that ? – MasterJoe Sep 07 '20 at 19:06

Goca · Answer 5 · 2014-05-20T20:06:45.170

4

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

edited May 20 '14 at 20:06

answered May 19 '14 at 18:13

Goca

1,743
2
15
36

score 4 · Answer 6 · edited May 07 '21 at 00:39

4

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

edited May 07 '21 at 00:39

Martijn Pieters

1,048,767
296
4,058
3,343

answered Aug 07 '17 at 07:45

Satinder singh

10,100
16
60
102

score 3 · Answer 7 · edited Jan 15 '16 at 10:22

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

score 2 · Answer 8 · answered Jun 26 '09 at 22:12

2

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

answered Jun 26 '09 at 22:12

Eric

92,005
12
114
115

score 1 · Answer 9 · edited Jul 08 '14 at 03:36

1

This works for me, using Microsoft SQL Server 2005:

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

edited Jul 08 '14 at 03:36

nstCactus

5,141
2
30
41

answered Jul 08 '14 at 03:11

Aulia

11
1

score 1 · Answer 10 · answered Feb 09 '17 at 13:30

1

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

answered Feb 09 '17 at 13:30

David

21
1

score 1 · Answer 11 · answered Feb 09 '17 at 13:43

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)

score 1 · Answer 12 · edited Feb 09 '17 at 14:41

1

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

edited Feb 09 '17 at 14:41

Dan Lowe

51,713
20
123
112

answered Feb 09 '17 at 14:16

David

21
1

score 1 · Answer 13 · answered Nov 02 '18 at 07:49

1

This query can also be used.

DECLARE @SelectedDate DATE =  GETDATE()

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

answered Nov 02 '18 at 07:49

Serkan Arslan

13,158
4
29
44

score 1 · Answer 14 · answered Sep 05 '22 at 14:53

--## 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]

There are currently 21 answers with the top-voted answer having over 80 upvotes. What does your answer add to the thread? What are you doing that’s not covered by the other answers? Please [edit] your answer to help explain WHY these are useful date functions and HOW they answer the original question. — Jeremy Caney, Sep 10 '22 at 00:56

score 0 · Answer 15 · answered Jun 27 '09 at 00:28

0

My 2 cents:

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

Raj

answered Jun 27 '09 at 00:28

Raj

10,653
2
45
52

score 0 · Answer 16 · answered May 20 '13 at 10:49

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.

score 0 · Answer 17 · edited Jan 15 '16 at 10:22

0

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

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

edited Jan 15 '16 at 10:22

marc_s

732,580
175
1,330
1,459

answered Apr 01 '14 at 13:59

Trilok

9
1

score 0 · Answer 18 · answered Sep 17 '14 at 21:03

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

Başar Kaya · Answer 19 · 2017-12-20T07:58:20.827

0

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

edited Dec 20 '17 at 07:58

answered Dec 20 '17 at 07:40

Başar Kaya

354
6
13

Why on earth would you write your own function to do this when you can just cast EOMONTH to DATETIME? select [dbo].[fn_GetLastDate]('1900-02-03') Won't return the correct result. – Martin Smith Jan 05 '18 at 19:45
Martin, you are right. I don't want to give an alternative advice to the world:). Just, I need this function before Sql server 2012 versions. – Başar Kaya Jan 06 '18 at 12:22
It won't work on versions before 2012 as it uses CHOOSE. – Martin Smith Jan 06 '18 at 12:55

score 0 · Answer 20 · edited Nov 09 '20 at 12:17

0

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?

edited Nov 09 '20 at 12:17

ZF007

3,708
8
29
48

answered Nov 09 '20 at 11:27

Siluxmedia

27
4

score 0 · Answer 21 · answered Dec 23 '21 at 06:04

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

score -1 · Answer 22 · edited Jan 16 '19 at 00:21

-1

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

edited Jan 16 '19 at 00:21

Nathan Champion

1,291
1
14
23

answered Jan 15 '19 at 20:14

radouans

11
1

Get the last day of the month in SQL

22 Answers22

Linked

Related