Last day of each Month

building reports
1

Hi All

How do I get the last day of each month (Jan to Dec) for the current year

eg Jan 31/01/2021
Feb 29/02/2021
etc…

Many thanks all
Zahed

2

What database are you running on? You could do this in free-hand SQL. I have a Calendar table in Microsoft SQL Server and here is what the free-hand SQL in my environment would look like…

SELECT DISTINCT DATEADD(MONTH, DATEDIFF(MONTH, 0, dbo.Calendar.dt) + 1, 0) - 1
FROM dbo.Calendar
WHERE dbo.Calendar.Y = 2021

Does that help?

3

If you do not have Calendar table you can create on on the fly in free-hand SQL…

DECLARE @START_DATE DATETIME;
DECLARE @ENDDATE DATETIME;
SET @START_DATE = '2021-01-01';
SET @ENDDATE = '2021-12-31';

WITH CTE_DATES AS
(
    SELECT @START_DATE DateValue
    UNION ALL
    SELECT DATEADD (DAY, 1, DateValue)
    FROM   CTE_DATES
    WHERE  DATEADD (DAY, 1, DateValue) <= @ENDDATE
)
SELECT DISTINCT
       DATEADD (MONTH, DATEDIFF (MONTH, 0, DateValue) + 1, 0) - 1 AS [Last Day of Month]
FROM   CTE_DATES
OPTION (MAXRECURSION 365); -- number of days in a year

Here is a demo of that code.

4

You can also use the EOMONTH() function.
So instead of DATEADD(MONTH, DATEDIFF(MONTH, 0, dbo.Calendar.dt) + 1, 0) - 1, you can use EOMONTH(dbo.calendar.dt)

1 Like
5

My favorite logic for this is to get the first day of the next month and subtract 1. This will work no matter how many days are in a month. In SQL Server, this might look like

Select 
  DATEFROMPARTS(
  (Case
    when month(GetDate()) = 12 then Year(GetDate()) + 1
	else Year(GetDate())
  end),
  (Case
    when month(GetDate()) = 12 then 1
	else month(GetDate()) + 1
  end),
  1)

Or, if you’re using SQL Server 2016 or newer, even easier would be

EOMONTH(GetDate())

In Oracle, it would look something like this:

Select
To_Date(
 '01-' +
  (Case
    when Trunc(Sysdate, 'MM') = 12 then 'JAN'
	else Trunc(Sysdate, 'MON')
   end) + '-' +
  To_Char(Case
            when Trunc(Sysdate, 'MM') = 12 then Trunc(Sysdate, 'YYYY') + 1
			else Trunc(Sysdate, 'YYYY')
		  end)
)
from DUAL

NOTE: I haven’t tested the Oracle code because I don’t have an active Oracle system handy, but I’m pretty sure that will work.

-Dell