Wednesday, 3 September 2014

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


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

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

set @dtweek = datepart(weekday, @date) -1
Set @weekstart = DateAdd(Day,-@dtweek,@date)


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

SELECT @weekstart = @t


END

return @weekstart

end



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

No comments:

Post a Comment