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
)

No of Weekdays in a year

I posted this over at MSDN T-SQL forum

How to find number of weekdays in a year.

1 year = 365 or 366 days = 52 week
1 week = 5 week days

so minimum there will be 52 x 5 week days in a year. So basically only need to determine the 365^th day or 366th day of the year is it a week day. This is handle by the 2 case statements in the query

declare @year int,
@boyear datetime,
@eoyear datetime

select @year = 2009
select @boyear = dateadd(year, @year - 1900, 0),
       @eoyear = dateadd(year, @year - 1900 + 1, -1)

select weekdays_year = (52 * 5)
                     + case when dateadd(day, (52 * 7), @boyear) <> @eoyear and datename(weekday, dateadd(day, (52 * 7), @boyear)) not in ('Saturday', 'Sunday')
                            then 1
                            else 0
                            end
                     + case when datename(weekday, @eoyear) not in ('Saturday', 'Sunday')
                            then 1
                            else 0
                            end