We know that in sql server we have a lib function for week of year calculation.
But we need to know the calculation behind that. Here is the actual calculation given below.
But we need to know the calculation behind that. Here is the actual calculation given below.
DECLARE
@Inputdate DATE='2015-01-01'
DECLARE
@ORDINAL INT
DECLARE @RESULT FLOAT
IF
(datepart(yy,@Inputdate)%100=0 and datepart(yy,@Inputdate)%4=0)
BEGIN
SET
@ORDINAL=
CASE WHEN
datepart(mm,@Inputdate)=1 then 0
WHEN datepart(mm,@Inputdate)=2 then 31
WHEN datepart(mm,@Inputdate)=3 then 60
WHEN datepart(mm,@Inputdate)=4 then 91
WHEN datepart(mm,@Inputdate)=5 then 121
WHEN datepart(mm,@Inputdate)=6 then 152
WHEN datepart(mm,@Inputdate)=7 then 182
WHEN datepart(mm,@Inputdate)=8 then 213
WHEN datepart(mm,@Inputdate)=9 then 244
WHEN datepart(mm,@Inputdate)=10 then 274
WHEN datepart(mm,@Inputdate)=11 then 305
ELSE
335 END
SET
@ORDINAL=@ORDINAL+datepart(DD,@Inputdate)
SET
@RESULT=(@ORDINAL-(datepart(DW,@Inputdate)-1)+10)/7
SELECT CAST(@RESULT AS INT) WEEK_OF_YEAR
END
ELSE
BEGIN
SET
@ORDINAL=
CASE WHEN
datepart(mm,@Inputdate)=1 then 0
WHEN
datepart(mm,@Inputdate)=2 then 31
WHEN
datepart(mm,@Inputdate)=3 then 59
WHEN
datepart(mm,@Inputdate)=4 then 90
WHEN
datepart(mm,@Inputdate)=5 then 120
WHEN
datepart(mm,@Inputdate)=6 then 151
WHEN
datepart(mm,@Inputdate)=7 then 181
WHEN
datepart(mm,@Inputdate)=8 then 212
WHEN
datepart(mm,@Inputdate)=9 then 243
WHEN
datepart(mm,@Inputdate)=10 then 273
WHEN
datepart(mm,@Inputdate)=11 then 304
ELSE 334 END
SET
@ORDINAL=@ORDINAL+datepart(DD,@Inputdate)
SET
@RESULT=(@ORDINAL-(datepart(DW,@Inputdate)-1)+10)/7
SELECT CAST(@RESULT AS INT) WEEK_OF_YEAR
END
No comments:
Post a Comment