Sql Query to Find Last Day of Current Month

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

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)

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

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

How can I get the last day of periodic months in SQL?

Updated 2022-04-01

If I'm understanding correctly, you want to return the same "day" for each month - except when @StartDate is the last day of the month.

One approach would be to determine if the @StartDate is the last day of the month. If so, use EOMONTH() to return the last day in each of the subsequent months. Otherwise, use DATEADD() to return the specified "day" in each month. This approach should work for any date.

One approach is as follows:

1. If Maturity Date is last day of month, OR Maturity Day of month is > number of days in subsequent month, use EOMONTH() to return the last day of that month
2. Otherwise, use DATEADD() and DATEFROMPARTS() to generate the next date using the Maturity Day of month

SQL:

-- Note: Using 12 months for demo only
; WITH dates AS (
  SELECT @StartDate AS MaturityDate
         , IIF(@StartDate = EOMONTH(@StartDate), 1, 0) AS IsEOM
  UNION ALL
  SELECT 
         CASE -- Maturity date is last day of month OR 
              -- Maturity "day" is > number of days in current month
              WHEN IsEOM = 1 OR DAY(@StartDate) > DAY( EOMONTH(NextMaturityDate) ) 
                    THEN EOMONTH( DATEADD(MONTH, 1, MaturityDate ))
              -- Otherwise, maturity "day" is valid for current month
              ELSE DATEFROMPARTS(
                         Year(NextMaturityDate)
                         , Month(NextMaturityDate)
                         , DAY(@StartDate)
                      )
         END
         , IsEOM
  FROM  ( SELECT MaturityDate
                 , IsEOM
                 , DATEADD(MONTH, 1, MaturityDate) AS NextMaturityDate
          FROM  dates
        ) t
  WHERE MaturityDate < @EndDate
)
SELECT MaturityDate AS [vade] 
FROM   dates 
OPTION (MAXRECURSION 0)

Results for 2022-03-31 (Last Day Of Month)

set dateformat dmy

select dateadd(d, -1, dateadd(m, 1, '01-' + convert(varchar, month(getdate())) + '-' + convert(varchar, year(getdate()))))