We all know how to find the first and last day of a year. The first day is always January 1 and the last day of the year is December 31. How about first and last day of a month ? or maybe week or maybe quarter.
There are lots of way of doing this, but this is my preferred way.
To find the beginning and ending of current year
select begin_of_curr_year = dateadd(year, datediff(year, 0, getdate()), 0),
end_of_curr_year = dateadd(year, datediff(year, 0, getdate()) + 1, -1)
What about a specific year ?
declare @year int
select @year = 2010
select begin_of_selected_year = dateadd(year, @year - 1900, 0),
end_of_selected_year = dateadd(year, @year - 1899, -1)
And put it as a function
create function dbo.fn_begin_end_of_period
(
@type int, -- 1 : year, 2 : quarter, 3 : month, 4 : week
@year int,
@period int -- ignore for year
)
returns table
as
return
(
select begin_of_period = case @type
when 1 then dateadd(year, @year - 1900, 0)
when 2 then dateadd(quarter, @period - 1, dateadd(year, @year - 1900, 0))
when 3 then dateadd(month, @period - 1, dateadd(year, @year - 1900, 0))
when 4 then dateadd(week, @period - 1, dateadd(year, @year - 1900, 0))
end,
end_of_period = case @type
when 1 then dateadd(year, @year - 1899, -1)
when 2 then dateadd(quarter, @period, dateadd(year, @year - 1900, -1))
when 3 then dateadd(month, @period, dateadd(year, @year - 1900, -1))
when 4 then dateadd(week, @period, dateadd(year, @year - 1900, -1))
end
)