marți, 5 decembrie 2017

SQL Server - First day and last day of a month

How to select first day of the month and the last day of the month.

DECLARE @my_date DATE
SET @my_date = GETDATE()

DECLARE @offset_months_first int
SET @offset_months_first = -5
DECLARE @offset_months_last int
SET @offset_months_last = -12

select DATEADD(DAY, -1 * DAY(@my_date) + 1,DATEADD(MM, 0 + @offset_months_first, @my_date)) as first_day_of_the_month
select DATEADD(DAY, -1 * DAY(@my_date), DATEADD(MM, 1 + @offset_months_last, @my_date)) as last_day_of_the_month