SQL SERVER – Find Week of the Year Using DatePart Function

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.

SQL SERVER - Find Week of the Year Using DatePart Function weekoftheyear

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.

  1. Week of the Year
  2. First Date of the Week
  3. Last Date of the Week

Let us execute the following query and you will find necessary details.

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'

SQL SERVER - Find Week of the Year Using DatePart Function sysdatetime

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)

SQL DateTime, SQL Function, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Delete All Waiting Workflows in MSCRM to Speed Up Microsoft Dynamics
Next Post
SQL SERVER – Understanding How to Play With Row Constructor

Related Posts

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!



Leave a Reply