Technical Article

Sproc to get First Day of the Month,First Weekday ETC

,

First Day of the Month, First Weekday, Total Days in this Month, Days In First WeeK, Days In Last Week,Total Weekend Days

sample output:

Given Date:Jul 16 2005 12:00AM

First Day of the Month:7/1/2005

First Weekday (sun=1,sat=7):6

Total Days in this Month:31

Days In First Week:2

Days In Last Week:1

Total Weekend Days:10

/*********************************************************************
*	total days in the month and total days left
*	sample Execution
*		exec AllInOneDateOperations '7/16/05'
**********************************************************************/
Alter proc AllInOneDateOperations 
        @givendate datetime
as
declare	@totalDaysOfMonth int,
	@firstDayOfMonth varchar(20),
	@firstWeekday int,
	@totalWeekendDays int,
	@daysInLastWeek int,
	@daysInFirstWeek int,
	@fullWeeks int


	print '	Given Date:' + cast(@givenDate as char(20))

set @firstDayOfMonth =  cast(month(@givenDate) as varchar(2))+'/'+'1'
			+'/'+ cast(year(@givenDate) as varchar(4))
	print '	First Day of the Month:' + cast(@firstDayOfMonth as varchar(20))

set @firstWeekday = datepart(dw, @firstDayOfMonth )
	print '	First Weekday (sun=1,sat=7):' + cast(@firstWeekday as char(10))

set @totalDaysOfMonth = datepart(dd, dateadd(mm,1,@givenDate-day(@givenDate)+1)-1)
	print '	Total Days in this Month:' + cast(@totalDaysOfMonth as char(10))

set @daysInFirstWeek = 8 - @firstWeekday
	print '	Days In First Week:' + cast(@daysInFirstWeek as char(10))

-- when it is 7, means there is a sunday and a saturday.
if (@daysInFirstWeek = 7)
	set @totalWeekendDays = 2
else	set @totalWeekendDays = 1

-- this fullweeks will not include the first week even if it has 7 days
set @fullWeeks = (@totalDaysOfMonth - @daysInFirstWeek)/7

set @totalWeekendDays = @totalWeekendDays + (@fullWeeks * 2)

set @daysInLastWeek = @totalDaysOfMonth - (@daysInFirstWeek + (@fullWeeks*7))
	print '	Days In Last Week:' + cast(@daysInLastWeek as char(10))
if ( @daysInLastWeek >= 1 )
	set @totalWeekendDays = @totalWeekendDays + 1

	print '	Total Weekend Days:' + cast(@totalWeekendDays as char(10))

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating