SQL code to show expiration date of contract prior to report run date AND in next month?

Question

I’m new to SQL coding and am currently trying to figure out how to write a line of code in a report query that will show me contract expiration dates that are prior to the report run date AND contract dates that are set to expire in the next month. Example: report runs on Sept. 1st to show every contract expired prior to date and contracts expiring in the month of October.

I have not tried anything yet and haven’t had much luck finding anything online that is as complex as what I need the report to do.

Answer 1

If you have a date table, this should be simple but as you've not mentioned it, I'll assume you don't. I highly recommend adding a dates/calendar table to your database as it will help you in the future, there are plenty of blogs about how to quickly create one...

Anyway, the following will give you the end of the following month, based on today's date. It works by going two months into the future, getting the 1st date for that month then going back a single date to land on the last day of the previous month.

E.g. We are at 5th sept. Advance two months to get to 5th Nov, get the first day of this month (1st Nov) then take 1 day from this to get to the 31st Oct.

SELECT DATEADD(d, -1,                             -- subtract 1 day from the 1st day of date in two months time.
                DATEFROMPARTS(                    --     build a date from the following
                  year(dateadd(m, 2, getdate()))  --        get the year of the date in two month
                , month(dateadd(m, 2, getdate())) --        get the month of the date in two month
                , 1                               --        fix to the first of the month
                )    
            )

By getting the 1st day of two months in advance, we don't have to worry about how many days each month has, this will also work when years are spanned. So if you run this today, you'll get 2022-01-31 returned.

So now in your query you can do something like

DECLARE @lastDate DATE 
SET @lastDate = DATEADD(d, -1,                             -- subtract 1 day from the 1st day of date in two months time.
                DATEFROMPARTS(                    --     build a date from the following
                  year(dateadd(m, 2, getdate()))  --        get the year of the date in two month
                , month(dateadd(m, 2, getdate())) --        get the month of the date in two month
                , 1                               --        fix to the first of the month
                )    
            )

SELECT * FROM MyTable WHERE ContractExpriryDate <= @lastDate

You could also create a function to return this value so you can reuse it easier.