Find Week in Given Date in SQL Server




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.


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: