Get The First Day Of The Month From 3 Months Ago Using T-SQL

Dealing with dates in SQL Server is a critical skill and required when developing reports that depend on the Date, Day and Hour to be accurate. This includes logically moving through the year or bracketing results inside a month in your code. Here are some examples of how to get a previous month, future months and the 1st and last day of that month.

-- End of month
SELECT EOMONTH ( '02/12/2051' )  -- 2051-02-28

-- 1st of month 3 months ago
SELECT DATEADD(month, -3, DATEADD(day, 1-day(GETDATE()), GETDATE()))

-- Next month
SELECT DATEADD(month, 1, DATEADD(day, 1-day(GETDATE()), GETDATE()))

First Day of Next Month

When looking for the first day of the month the first choice might be to simply replace the day with a 1 and build the remainder of the Date using a string. Very Messy…

SELECT CONVERT(VARCHAR, (DATEPART(mm, GETDATE()))) + '/1/' + CONVERT(VARCHAR, (DATEPART(yyyy, GETDATE())))

There’s many things wrong with this statement even though it returns the first of this month as a string. The first day of the NEXT month requires changing the month and day values then casting as a DATE data type.

We can get today’s date for next month by using DATEADD and adding (or subtracting) a month.

SELECT DATEADD(month, 1, GETDATE())  -- includes time
SELECT CONVERT(DATE, DATEADD(month, 1, GETDATE()))  -- Date only
SELECT CONVERT(DATE, DATEADD(month, -1, GETDATE())) -- Last month

First Day Of The Month From 3 Months Ago

Now we need to find the 1st day of the month. We can use the DATEADD function to subtract the month’s and then the DAY function to return the day as a integer and subtract.

SELECT 1 - DAY(GETDATE()) -- negative integer current day minus 1

SELECT DATEADD(month, -3, DATEADD(day, 1-day(GETDATE()), GETDATE()))

Now that we have a DAY function that provides the 1st of the month, we only need to change the “month” value for DATEADD to get 3 months ago, next month or 3 months forward.

SELECT DATEADD(month, -3, DATEADD(day, 1-day(GETDATE()), GETDATE()))
SELECT DATEADD(month, 1, DATEADD(day, 1-day(GETDATE()), GETDATE()))
SELECT DATEADD(month, 3, DATEADD(day, 1-day(GETDATE()), GETDATE()))

T-SQL – EOMONTH (End Of Month) Function

One last note,.. the EOMONTH function (post SQL 2012) simplifies the process to return the end of the month date with a sub-function to add a month to the results. Here are some examples…

SELECT EOMONTH ( '03/15/2050' )  -- 2050-03-31
SELECT EOMONTH ( '12/09/2053' )  -- 2053-12-31

Depending on the year you may or may not be dealing with a Leap Year where the end of Month like, February and April, will have an additional day. The function is able to handle this too.

SELECT EOMONTH ( '02/12/2051' )  -- 2051-02-28
SELECT EOMONTH ( '02/12/2052' )  -- 2052-02-29
SELECT EOMONTH ( '04/21/2056' )  -- 2056-04-30

The EOMONTH function also accepts a second parameter where you can add or subtract months to the results. Using the same examples above, we can add or subtract a month to the final date result.

SELECT EOMONTH ( '03/15/2050', 11)   -- 2051-02-28
SELECT EOMONTH ( '12/09/2053', 4 )   -- 2054-04-30
SELECT EOMONTH ( '06/19/2051', 4 )   -- 2051-10-31
SELECT EOMONTH ( '02/12/2051', -16)  -- 2049-10-31
SELECT EOMONTH ( '04/21/2056', -27)  -- 2054-01-31

---