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

  • 1
    Can you explain a little bit how you get to October from a December input? – sp_BlitzErik Dec 6 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 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);

Incidentally, I just gave a whole presentation on this topic this morning.

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 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]
ADD     CONSTRAINT [PK_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

-- https://catalog.data.gov/dataset/federal-holidays/resource/28f55f36-dbff-4b9b-9092-ad8b8d679ef7

-- Populate Calendar

INSERT [Calendar] 
(
    [Date],
    [Year],
    [Month],
    [Month Name],
    [Day],
    [Day Name],
    [Day Of Year],
    [Weekday],
    [Year Week]
) 
SELECT  DATEADD(DAY, [Number]-1, '19000101'),
        YEAR(DATEADD(DAY, [Number]-1, '19000101')),
        MONTH(DATEADD(DAY, [Number]-1, '19000101')),
        DATENAME(MONTH, (DATEADD(DAY, [Number]-1, '19000101'))),
        DAY((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]
ADD     CONSTRAINT [PK_Calendar]
        PRIMARY KEY CLUSTERED ([Date]);
GO

-- CREATE NONCLUSTERED INDEX Calendar_Year ON Calendar (Year);
-- CREATE NONCLUSTERED INDEX Calendar_Month ON Calendar (Month);
-- CREATE NONCLUSTERED INDEX Calendar_Day ON Calendar (Day);
-- ...

SELECT  TOP (100) * 
FROM    [Calendar];
GO
  • This would definitely be the "Ideal" solution that I can towards building into the process. Great insight, thank you! – Mike Jones Dec 6 at 20:13

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.