Tuesday, July 28, 2009

Find Beginning and Ending of Period

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
)

1 comment:

  1. Followed you here from SQLTeam.com... Great posts. I have to work at lot with dates and the information you've provided here over several posts has been ultra helpful. Thanks!

    ReplyDelete