As a developer I believe that most of the times we need to built our query based on date column. Some times we need to get or find out the week number from a given date. Sql server provides us such an easy way to find out the week number from a date. The function is DATEPART. By using DATEPART function we can calculate week number easily. Please follow my example code to achieve the expected result.
To Do the Example Furst Create the Below Database Table:
CREATE TABLE [dbo].[Employee] ( [ID] [int] NULL, [Name] [varchar](200) NULL, [JoiningDate] [smalldatetime] NULL )
Now populate the table with some Data:
INSERT INTO EMPLOYEE VALUES(1,'Shawpnendu','Jan 01, 2012') INSERT INTO EMPLOYEE VALUES(2,'Bimalandu','Jan 10, 2012') INSERT INTO EMPLOYEE VALUES(3,'Purnendu','Jan 20, 2012') INSERT INTO EMPLOYEE VALUES(4,'Amalendu','Jan 30, 2012') INSERT INTO EMPLOYEE VALUES(5,'Chadbindu','Feb 05, 2012')
SQL to Get the Week Number from Dtae Time Fields:
SELECT *,DATEPART(wk,JoiningDate) [Week Number] FROM EMPLOYEE