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)