How to Get Week Numbers from Dates

Get week number in year from date

You probably need to specify the first day of your week with set datefirst:

set datefirst 1;
select datepart(week, '2017-02-01');

returns 6

---

Depending on the default language, your datefirst might be set to 7.

set datefirst 7;
select datepart(week, '2017-02-01');

returns 5

rextester demo: http://rextester.com/KEPB52852

How do I calculate the week number given a date?

Pseudocode:

int julian = getDayOfYear(myDate)  // Jan 1 = 1, Jan 2 = 2, etc...
int dow = getDayOfWeek(myDate)     // Sun = 0, Mon = 1, etc...
int dowJan1 = getDayOfWeek("1/1/" + thisYear)   // find out first of year's day
// int badWeekNum = (julian / 7) + 1  // Get our week# (wrong!  Don't use this)
int weekNum = ((julian + 6) / 7)   // probably better.  CHECK THIS LINE. (See comments.)
if (dow < dowJan1)                 // adjust for being after Saturday of week #1
    ++weekNum;
return (weekNum)

To clarify, this algorithm assumes you number your weeks like this:

S  M  T  W  R  F  S
            1  2  3    <-- week #1
4  5  6  7  8  9 10    <-- week #2
[etc.]

getDayOfWeek() and getDayOfYear() are standard date-object operations in most languages. If yours doesn't have them, you can count-forward from some known date (Jan 1, 1970 is a common one), after looking up to see what day of the week it was.

If you're going to implement your own date counting routines, remember that years that are divisible by 100 are NOT leap years, unless they are also divisible by 400. So 1900 was not a leap year, but 2000 was. If you're going to work far back in time, you have to mess with Gregorian vs Julian calendars, etc., see Wikipedia for loads of info on that.

This link talks about date/time functions in Windows/C++ in greater detail.

Fetch week numbers with date range of each week for a whole year

There are a lot of ways to solve this problem. A brute force approach generates all the dates using a some method -- this uses a recursive CTE -- and then assigns the weeks based on a window function counting the Mondays.

with cte as (
      select datefromparts(2021, 1, 1) as dte
      union all
      select dateadd(day, 1, dte)
      from cte
      where dte < datefromparts(2021, 12, 31)
     )
select weeknum, min(dte), max(dte)
from (select cte.*,
             sum(case when datename(weekday, dte) = 'Monday' then 1 else 0 end) over (order by dte) as weeknum
      from cte
     ) cte
group by weeknum
option (maxrecursion 0);

This is fine for a one-off query, but isn't the most efficient method to generate the weeks. It is, however, a fun illustration of recursive CTEs. That said, you should probably really have a calendar table of some sort that encapsulates this information as a table.

How to get week number from date input in javascript?

function getWeekNumber(thisDate) {
var dt = new Date(thisDate);
var thisDay = dt.getDate();

var newDate = dt;
newDate.setDate(1); // first day of month
var digit = newDate.getDay();

var Q = (thisDay + digit) / 7;

var R = (thisDay + digit) % 7;

if (R !== 0) return Math.ceil(Q);
else return Q;}

getWeekNumber("07/31/2016");

How to get the week number, start date and end date of Compelete year in SQL Server?

If you are interested in 2019 only, then the following code will produce exactly what you are looking for:

DECLARE @weekNum INT = 1;

WITH Weeks AS (
    SELECT @weekNum AS WeekNo
    UNION ALL
    SELECT WeekNo + 1 FROM Weeks WHERE WeekNo + 1 <= 53
)
SELECT  WeekNo,
        CASE
          WHEN WeekStartDate < '2019-01-01' THEN CONVERT(DATE, '2019-01-01')
          ELSE CONVERT(DATE, WeekStartDate)
        END AS WeekStartDate,
        CASE
          WHEN WeekEndDate > '2019-12-31' THEN CONVERT(DATE, '2019-12-31')
          ELSE CONVERT(DATE, WeekEndDate)
        END AS WeekEndDate
  FROM  (
        SELECT  WeekNo,
                DATEADD(WEEK, WeekNo - 1, '2018-12-30') AS WeekStartDate,
                DATEADD(WEEK, WeekNo - 1, '2019-01-05') AS WeekEndDate
          FROM  Weeks
        ) a

OUTPUT:

WeekNo  WeekStartDate   WeekEndDate
1       2019-01-01      2019-01-05
2       2019-01-06      2019-01-12
3       2019-01-13      2019-01-19
4       2019-01-20      2019-01-26
5       2019-01-27      2019-02-02
6       2019-02-03      2019-02-09
7       2019-02-10      2019-02-16
8       2019-02-17      2019-02-23
...
51      2019-12-15      2019-12-21
52      2019-12-22      2019-12-28
53      2019-12-29      2019-12-31

Edit following OP comment about variable start and end dates

Following OP's comment about varying start and end dates, I've revisited the code and made it such that is can work between any two dates:

DECLARE @startDate DATE = CONVERT(DATE, '2019-01-01');
DECLARE @endDate DATE = CONVERT(DATE, '2019-12-31');
DECLARE @weekNum INT = 1;

WITH Weeks AS (
    SELECT @weekNum AS WeekNo
    UNION ALL
    SELECT WeekNo + 1 FROM Weeks WHERE WeekNo + 1 <= DATEDIFF(WEEK, @StartDate, @EndDate) + 1
)
SELECT  WeekNo,
        CASE
          WHEN WeekStartDate < @startDate THEN @startDate
          ELSE CONVERT(DATE, WeekStartDate)
        END AS WeekStartDate,
        CASE
          WHEN WeekEndDate > @endDate THEN @endDate
          ELSE CONVERT(DATE, WeekEndDate)
        END AS WeekEndDate
  FROM  (
        SELECT  WeekNo,
                DATEADD(WEEK, WeekNo - 1, OffsetStartDate) AS WeekStartDate,
                DATEADD(WEEK, WeekNo - 1, OffsetEndDate) AS WeekEndDate
          FROM  Weeks
            INNER JOIN  (
                        SELECT  CASE
                                    WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN @startDate
                                    ELSE DATEADD(DAY, 1 - DATEPART(WEEKDAY, @startDate), @startDate)
                                END AS OffsetStartDate,
                                CASE
                                    WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN DATEADD(DAY, 6, @startDate)
                                    ELSE DATEADD(DAY, 7 - DATEPART(WEEKDAY, @startDate), @startDate)
                                END AS OffsetEndDate
                        ) a ON 1 = 1
        ) a

Simply modify @startDate and @endDate to reflect the desired start and end dates. The format of the string is YYYY-MM-DD.

This will output a variable number of weeks between the two dates, starting and ending on the specified date (creating partial weeks as needed). Hopefully, as per the requirement.

How is the week number calculated based on a date?

How are these numbers derived?

Well, that depends on what system you're using, but I suspect you're after the ISO-8601 week number, which is defined like this:

2.2.10 calendar week number

ordinal number which identifies a calendar week within its calendar year according to the rule that the first calendar week of a year is that one which includes the first Thursday of that year and that the last calendar week of a calendar year is the week immediately preceding the first calendar week of the next calendar year

Bear in mind that a week in ISO-8601 starts on Monday and finishes on Sunday - so another way of expressing the "first Thursday" rule is that the first week of the year is the first week containing at least four days of the new year.

Another vital point is that when you're expressing values using "week of week year", you need to use the "week year" itself as well, not the normal year. So for Sunday January 3rd 2010, you might express this as "Week 53, week year 2009, day-of-week Sunday". It's all too easy to use the "wrong type of year" and end up messing up values around the end of December and start of January.

---

Related Topics