As I blog every single day, lots of people ask me if I have to build a calender for the same. The answer is yes, I do not have a personal calender and I keep a note of all the ideas in the calender. The next day when I get ready to blog, I use the same calender as a reference to blog about the same. Most of the time I plan out my week at a time. Â Let us learn about how to find week of the year using datepart function.
Here is one of the functions which I use all the time personally. This function gives me three important details to me which I am looking for.
- Week of the Year
- First Date of the Week
- Last Date of the Week
Let us execute the following query and you will find necessary details.
SELECT DATEPART( WK, SYSDATETIME()) 'Week of the Year', CONVERT(VARCHAR(10), DATEADD(dd, -(DATEPART(dw, SYSDATETIME())-1), SYSDATETIME()) ,111) 'First Date of theWeek', CONVERT(VARCHAR(10), DATEADD(dd, 7-(DATEPART(dw, SYSDATETIME())), SYSDATETIME()) ,111) 'Last Date of the Week'
I know some of you will say that I should have used DATE datatype, but I still use CONVERT function as it works in pretty much all the versions of SQL Server. As a consultant, I like to write scripts which work in most versions of SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
As you mentioned, you have used CONVERT instead of DATE datatype because DATE datatype not available in previous version of SQL server (i.e. 2005 or earlier),
but still this query cannot work in all versions because SYSDATETIME is also not available in SQL server 2005.
Very true. I think for everybody who is using SQL Server 2005, I should have a new query which uses GETDATE() instead of SYSDATETIME()
thanks
Love your Blog! I get great and useful info from it. Thank you!
THIS IS ABOSLUTLY FUNCTIONAL … THX FOLKS …