Wednesday, 3 September 2014

How to get week end date for particular date in a month in sql server


CREATE function [dbo].[fn_WeekEndDate]
(
@date datetime
)
returns datetime
as
begin

declare @dtweek int
declare @weekend DateTime
Declare @NoOfDays int
Declare @t varchar(100)

set @dtweek = 7- datepart(weekday, @date)
Set @weekend = DateAdd(Day,@dtweek,@date)

if datepart(month,@weekend) <> datepart(month,@date)
BEGIN
Set @t = Convert(VARCHAR,DatePart(month,DateAdd(month,1,@date))) + '/01/'+ Convert(VARCHAR,datepart(year,@date))
Set @NoOfDays = DAteDiff(day,@t,@date) +1
Set @weekend = DateAdd(Day,-@NoOfDays,@date)

END
return @weekend

end


Ex : select dbo.[fn_WeekEndDate](getdate())

No comments:

Post a Comment