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
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
elseset @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))


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating