How to Calculate the Last Day of the Month in SQL

SQL Query to find the last day of the month

Try this one -

CREATE FUNCTION [dbo].[udf_GetLastDayOfMonth] 
(
    @Date DATETIME
)
RETURNS DATETIME
AS
BEGIN

    RETURN DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Date) + 1, 0))

END

Query:

DECLARE @date DATETIME
SELECT @date = '2013-05-31 15:04:10.027'

SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))

Output:

-----------------------
2013-05-31 00:00:00.000

Get the last day of the month in 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)

How do I calculate the last day of the month in SQL?

Here's a solution that gives you the last second of the current month. You can extract the date part or modify it to return just the day. I tested this on SQL Server 2005.

select dateadd( s, -1, dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 ) );

To understand how it works we have to look at the dateadd() and datediff() functions.

DATEADD(datepart, number, date)  
DATEDIFF(datepart, startdate, enddate)

If you run just the most inner call to datediff(), you get the current month number since timestamp 0.

select datediff(m, 0, getdate() );  
1327

The next part adds that number of months plus 1 to the 0 timestamp, giving you the starting point of the next calendar month.

select dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 );
2010-09-01 00:00:00.000

Finally, the outer dateadd() just subtracts one second from the beginning timestamp of next month, giving you the last second of the current month.

select dateadd( s, -1, dateadd( mm, datediff( m, 0, getdate() ) + 1, 0 ) );
2010-08-31 23:59:59.000

---

This old answer (below) has a bug where it doesn't work on the last day of a month that has more days than the next month. I'm leaving it here as a warning to others.

Add one month to the current date, and then subtract the value returned by the DAY function applied to the current date using the functions DAY and DATEADD.

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

SQL Query to find the last day of the month in 6 months time

If you are using SQL Server 2012+ you can use the eomonth function to get the last day of a month:

declare @d date = '2015-07-15'
select eomonth(@d,6)

result: 2016-01-31

The function takes a date and an optional integer that specifies the number of months to add to the date parameter.

SQL - How to tell if a date is the last day of the month?

If you are using SQL Server 2012 or above, use EOMONTH:

SELECT  my_date,
        IsEndOfMonth = IIF(my_date = EOMONTH(my_date), 1, 0) 
FROM    my_table

Return just the last day of each month with SQL

SQL Server (other DBMS will work the same or very similarly):

SELECT
  *
FROM
  YourTable
WHERE
  DateField IN (
    SELECT   MAX(DateField)
    FROM     YourTable
    GROUP BY MONTH(DateField), YEAR(DateField)
  )

An index on DateField is helpful here.

PS: If your DateField contains time values, the above will give you the very last record of every month, not the last day's worth of records. In this case use a method to reduce a datetime to its date value before doing the comparison, for example this one.

How to get the last day of the month using only month and year?

If you are using Sql Server 2012 then I'd use DATEFROMPARTS.

DECLARE @year SMALLINT = 2016
    ,@month TINYINT= 02

SELECT EOMONTH(DATEFROMPARTS(@year,@month,1))

---

Related Topics