## How do I calculate the week number given a date?

 Author Message Vaghasiya Nilesh Grasshopper Group: General Forum Members Points: 23 Visits: 35 hi guysHow do I calculate the week number given a date for current year? LutzM SSCertifiable Group: General Forum Members Points: 7021 Visits: 13559 There are several concepts depending on what type week number you're looking for.If you need the week numbered as per the restrictions described in BOL (BooksOnLine, the SQL Server help system usually installed together with SQL server), section "dateparts [SQL Server], DATEPART" then DATEPART is one option.If you're looking for the ISO week number, then you might want to have a look at BOL, section "create function, Example A". But please be aware that the function is designed as a scalar-valued function that might have performance issues. You should modify it to an inline table-valued function.Another option would be to build a calendar table. Please search this site for related scripts. If you need further assistance please post back what you've tried so far and where you get stuck. LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function Eralper SSC-Enthusiastic Group: General Forum Members Points: 182 Visits: 466 Hello,Easiest solution is using DATEPART function`select DATEPART(WEEK,GETDATE())`If that is not useful for your requirements, you can also think of to create date table in SQL Server EralperSQL Server and T-SQL Tutorials and ArticlesMicrosoft Certification and Certification Exams funooni SSC Veteran Group: General Forum Members Points: 209 Visits: 294 Going further from Eralper's idea;You can use `select DATEPART(WEEK,DAY(getdate())) `to get the week of the month.GETDATE() can be replaced by any datetime variable or even a string like '2010-12-07' ; i am using 'yyyy-dd-mm' format. Paul White SSChampion Group: General Forum Members Points: 10352 Visits: 11350 funooni (7/19/2010)Going further from Eralper's idea;To be fair, I think Lutz covered it first :-)You can use select DATEPART(WEEK,DAY(getdate())) to get the week of the month. GETDATE() can be replaced by any datetime variable or even a string like '2010-12-07' ; i am using 'yyyy-dd-mm' format.This does not work. The DAY built-in function returns just the day date part of the supplied date, so for 19th July 2010, it would return 19.Supplying the value 19 to the DATEPART function results in 19 being implicitly converted to a date (19 days after the base date '1900-01-01') to give '1900-01-20'. Knowing the week number of 20th January 1900 is unlikely to be the desired result ;-) Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi funooni SSC Veteran Group: General Forum Members Points: 209 Visits: 294 All marks go to Lutz ;-)This does not work. Well i just checked it and it does work.The DAY built-in function returns just the day date part of the supplied date, so for 19th July 2010, it would return 19.Yes this would return 19Supplying the value 19 to the DATEPART function results in 19 being implicitly converted to a date (19 days after the base date '1900-01-01') to give '1900-01-20'. Knowing the week number of 20th January 1900 is unlikely to be the desired result Supplying the value value 19 to DATEPART function with the first option 'WEEK', it will return 3 which means this is the 3rd week of the month.One more thing to add the function DATEPART(WEEK, will return the current week number.For instance,`select DATEPART(WEEK,21)`will return 4 as 3 weeks have finished.Let me know please if this does not work and correct me if i am wrong at perceiving this.Thanks Paul White SSChampion Group: General Forum Members Points: 10352 Visits: 11350 funooni (7/19/2010)Well i just checked it and it does work. Supplying the value value 19 to DATEPART function with the first option 'WEEK', it will return 3 which means this is the 3rd week of the month.We're after the week number of the year here, not the week number of the month :-)`DECLARE @dt DATETIME;SET @dt = '2010-07-19';SELECT DATEPART(WEEK, DAY(@dt)); -- 3SELECT DATEPART(WEEK, @dt); -- 30`The second example shows that 19th July 2010 is in week 30 of this year (with my current system settings).It's unclear to me what your code is showing. 19th July isn't in the third week of July on my calendar (July started on a Thursday, and weeks run from Monday for me).It is in the third week of July if you consider the first 7 days of any month to be the 'first week' and so on - is that the purpose of your function? If so, why does it return 4 for 20th July 2010? If I needed to know this sort of information, I would probably just use something like SELECT DAY(@dt) / 7 + 1; there's no need for the DATEPART at all.Paul Paul WhiteSQLPerformance.comSQLblog.com@SQL_Kiwi Eugene Elutin Hall of Fame Group: General Forum Members Points: 3046 Visits: 5478 funooni (7/19/2010)...Supplying the value value 19 to DATEPART function with the first option 'WEEK', it will return 3 which means this is the 3rd week of the month.One more thing to add the function DATEPART(WEEK, will return the current week number.For instance,`select DATEPART(WEEK,21)`will return 4 as 3 weeks have finished.Let me know please if this does not work and correct me if i am wrong at perceiving this.ThanksYou are wrong at perceiving how DATEPART function works!And it works exactly as explained by Paul White NZ.Check the BoL.The second input parameter of DATEPART function is of DATETIME datatype.When you supply the integer there, it is implicitly converted into datetime as per following:1 Jan 1900 + INT value.You can supply positive or negative integer values as you wish...The number of week will properly match only for years which week days match ones of year 1900! Last such year was 2007 and the next one will be 2018!So if you run select DATEPART(WEEK,364) it will return 53 always, regardless when (which year) you run it as it represents 31 Dec 1900!But correct week number for 31 Dec 2000 would be 54 - leap year man!select DATEPART(WEEK,'31 Dec 2000') _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help LutzM SSCertifiable Group: General Forum Members Points: 7021 Visits: 13559 Like I stated in my first post: It depends on the requirement.You cannot use DATEPART() if you need to get the week as per ISO specification.Furthermore, DATEPART(WEEK,somedate) will return different results based on the setting of DATEFIRST.Straight from BOL:The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year. Here are a few examples. Please note, that none of the DATEPART() function does return the correct ISO week and neither are the results consistent.`DECLARE @date DATETIMESET @date='20100102'--Saturday, January 1st, as per ISO definition week 53 of year 2009SET DATEFIRST 1SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNoSET DATEFIRST 2SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNoSET DATEFIRST 3SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNoSET DATEFIRST 4SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNoSET DATEFIRST 5SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNoSET DATEFIRST 6SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNoSET DATEFIRST 7SELECT @@DATEFIRST AS DATEFIRST,DATEPART(WEEK,@date) AS WeekNo` LutzA pessimist is an optimist with experience. How to get fast answers to your question How to post performance related questionsLinks for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function funooni SSC Veteran Group: General Forum Members Points: 209 Visits: 294 Thanks to all.Concepts cleared.