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
)

Monday, July 13, 2009

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 365th 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