# First and Last of Month Calculations

This is probably a rudimentary question for most, but for some reason I'm not getting it right. I currently use two specific SET formulas for "1st day of last month" and "Last day of last month"

1st day of last month (11/1, November 1st):

``````SET @strFirstofMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(MM, -1, @dtDate)), 0), 112))
``````

Last day of last month (11/30, November 30th):

``````SET @strLastofMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @dtDate))), -1), 112))
``````

I'm now needing a formula for the 1st day of the month before last, and the last day of the month before last

Example. If today is 12/6 (December 6), I need a formula for 10/1 (October 1st) and 10/31 (October 31st).

• Can you explain a little bit how you get to October from a December input? – Erik Darling Dec 6 '18 at 19:16
• If I was to run the code during any time throughout the month of December, using the two "1st Day of Month" and "Last Day of Month" functions in a "BETWEEN" statement. I should also mention that @dtDate = GETDATE() – Mike Jones Dec 6 '18 at 19:18

``````DECLARE @dtDate date = GETDATE();

-- month before last:
DECLARE @TwoMonthsAgo date = DATEADD(MONTH,-2,@dtDate);

-- first and last of that month:
SELECT
FirstOfMonth = DATEFROMPARTS(YEAR(@TwoMonthsAgo),MONTH(@TwoMonthsAgo),1),
LastOfMonth  = EOMONTH(@TwoMonthsAgo);
``````

But then what are you going to do with that? You can't say:

``````SELECT cols FROM dbo.table
WHERE datecol BETWEEN @FirstOfMonth AND @LastOfMonth;
``````

Why? Because if `datecol` also has time, you miss everything after midnight on the last day of the month.

Much better to just find the beginning of the current period, and from there it is trivial to find the beginning of the next period, and use an open-ended range instead of close-ended `BETWEEN`:

``````WHERE datecol >= @FirstOfMonth
AND datecol <  DATEADD(MONTH, 1, @FirstOfMonth);
``````

Some tips for further reading:

• Can not be stressed enough how much easier it is to go through code as it gets more and more complex if you type `DAY` or `MONTH` isntead of `d` or `m`. – Zane Dec 6 '18 at 20:38

Having a calendar table in your database will save you a lot of grief, and will come in handy more often than you think... In your case, you can use simple queries to figure out any 'last, first, next, previous' type of questions quickly and efficiently. For example, to get previous month's first and last days:

``````SELECT MIN(Date), MAX(Date)
FROM Calendar
WHERE [Year] = 2018 AND MONTH = 11;
``````

The numbers table which I use to populate the calendar will also come in very handy... You can play with the parameters and the initial date as your application requires. You may need to optimize the indexes on this table, but since it is typically very small, it shouldn't be an issue...

HTH

``````CREATE TABLE [Numbers]
(
[Number] INT NOT NULL
);

-- Populate Numbers
; WITH
[Level0]   AS (SELECT 1 AS [constant] UNION ALL SELECT 1),
[Level1]   AS (SELECT 1 AS [constant] FROM [Level0] AS [A] CROSS JOIN [Level0] AS [B]),
[Level2]   AS (SELECT 1 AS [constant] FROM [Level1] AS [A] CROSS JOIN [Level1] AS [B]),
[Level3]   AS (SELECT 1 AS [constant] FROM [Level2] AS [A] CROSS JOIN [Level2] AS [B]),
[Level4]   AS (SELECT 1 AS [constant] FROM [Level3] AS [A] CROSS JOIN [Level3] AS [B]),
[Sequential Numbers] AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS [Number] FROM [Level4])
INSERT INTO [Numbers] ([Number])
SELECT  [Number]
FROM    [Sequential Numbers];
GO

ALTER TABLE [dbo].[Numbers]
PRIMARY KEY CLUSTERED ([Number]);
GO

-- Calendar Table

CREATE TABLE [Calendar]
(
[Date]                  DATE NOT NULL,
[Year]                  SMALLINT NOT NULL,
[Month]                 TINYINT NOT NULL,
[Month Name]            NVARCHAR(10) NOT NULL,
[Day]                   TINYINT NOT NULL,
[Day Name]              NVARCHAR(10) NOT NULL,
[Day Of Year]           SMALLINT NOT NULL,
[Weekday]               NVARCHAR(10) NOT NULL,
[Year Week]             TINYINT NOT NULL--,
--[US Federal Holiday]  NVARCHAR(50) NULL,
--[Other Holiday]       NVARCHAR(50) NULL
);
GO

-- Populate Calendar

INSERT [Calendar]
(
[Date],
[Year],
[Month],
[Month Name],
[Day],
[Day Name],
[Day Of Year],
[Weekday],
[Year Week]
)
SELECT  DATEADD(DAY, [Number]-1, '19000101'),
DATENAME(MONTH, (DATEADD(DAY, [Number]-1, '19000101'))),
DATENAME(WEEKDAY, (DATEADD(DAY, [Number]-1, '19000101'))),
DATEPART(DAYOFYEAR, (DATEADD(DAY, [Number]-1, '19000101'))),
DATEPART(WEEKDAY, (DATEADD(DAY, [Number]-1, '19000101'))),
DATEPART(WEEK, (DATEADD(DAY, [Number]-1, '19000101')))
FROM    [Numbers]
WHERE   [Number] <= 65536;
GO

ALTER TABLE [dbo].[Calendar]