How to get week numbers for whole year in SQL Server

My scenario: I have SalesDate (eg: 2007-01-01 to 2016-01-01) filed like this up to todate and have daily date. Now I have to find this salesdate belong to which week.


  • Best way to get the latest version of a specific list of records in SQL
  • How to automatically run a stored procedure on scheduler basis?
  • Infinite loop in CTE when parsing self-referencing table
  • Generate xml from output clause
  • Adding Previous and Next Month Functionality to Calendar Report
  • Excel Text or General Formatted Date Import and Conversion in SQL Server
  • Start day of week is Sunday. Based on year, if new year starts week count start as 1.

    One Solution collect form web for “How to get week numbers for whole year in SQL Server”

    Try something like below,

    DECLARE @SalesDate DATETIME = '2007-01-01'
    SELECT DATEDIFF(WEEK, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, @SalesDate), 0)), 0), @SalesDate - 1) + 1 WeekD, @SalesDate SalesDate


    DECLARE @SalesDate DATETIME = '2007-01-01'
    SELECT DATEPART(WEEK,@SalesDate) WeekNumber, @SalesDate SalesDate
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.