In Many caseswe need to prepare the records on weekly basis. In this case, the below function is very useful to find the week in given date.
CREATE FUNCTION DBO.FINDINWEEK (@GIVENDATE DATETIME)
RETURNS VARCHAR(15)
AS
BEGIN
DECLARE @firstDayOfMonth VARCHAR(20),
@findWeek INT,
@weeks VARCHAR(30)
SET @firstDayOfMonth = CAST(MONTH(@givenDate) AS VARCHAR(2))+'/'+'1'+'/'+ CAST(YEAR(@givenDate) AS VARCHAR(4))
SET @findWeek= DATEPART(wk, @givendate) - DATEPART(wk, @firstDayOfMonth) + 1
SET @weeks = CASE @findWeek
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
WHEN 5 THEN 'Fifth'
WHEN 6 THEN 'Sixth'
ELSE 'Seventh'
END
RETURN @weeks + ' Week'
END
How to call this function from Sql sever?
select dbo.FindInWeek('02/05/2007')
Output
Second Week
The result shows second week because the date 02/05/2007 falls on a Monday which is the second week, considering the Sunday to Saturday as the week.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|