Get the Last Day of the Month in SQL

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)

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

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

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.

SQL query to find last day of current month?

Get the DateTime of Now

GETDATE() -- 2011-09-15 13:45:00.923

Calculate the difference in month's from '1900-01-01'

DATEDIFF(m, 0, GETDATE()) -- 1340

Add the difference to '1900-01-01' plus one extra month

DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0) -- 2011-10-01 00:00:00.000

Remove one second

DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0)) -- 2011-09-30 23:59:59.000

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.

---

Related Topics