SQL Server….

I am trying to select the week number from a date. Our company counts the first Monday of the year as week 1 with any days before that as week 0.

So example for 2022 with expected result….

1/1/2022 - Sat - Week 0
1/2/2022 - Sun - Week 0
1/3/2022 - Mon - Week 1
etc.

1/1/2023 - Sun - Week 0
1/1/2023 - Mon - Week 1
etc.

I have tried the 'week' and 'iso_week' functions plus setting the starting day of the week with 'datefirst' declaration, as well as various datepart statements.

I'm sure this is simpler than I am making it but too much googling and overthinking at this point. Maybe a set of case statements with math to derive the correct value depending on day counts and weekdays?

Appreciate any suggestions.

Seems like you should use datepart: https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql

but set the start of the first week to the first Monday of the year: https://stackoverflow.com/questions/13525710/first-monday-of-the-year-in-sql-server

Wondering if you could do as you say, figure out the first Monday of that year at 0:00. Define that in epoch time. If the prospective date in epoch time is less than that then that it's week 0. Else, ((prospective date - 1st Monday date)/60480000) + 1.

Basically use integer division to find the offset in weeks (60480000 miliseconds) between the two dates.

Edit: Saw you said year.

Per ISO8601, the week starts on Mondays, and the first week of the year contains Jan 4th, so it's already mostly built in for you. Use the DATEPART function to get the "iso_week" of that date.

DECLARE @date date = '2021-01-01';
SELECT DATEPART(iso_week, @date);

You'll run into weirdness when Jan 4th is a Fri, Sat, or Sun, but since you already have the counter, and you know the variations, it should be pretty simple to determine if an offset is required for that year.

For example, in 2020, Jan 1st was a Wednesday, and Jan 4th was a Saturday. So, iso_week 1 for that year would actually be your week 0, so you'll need to adjust for that in those years. If Jan 4th is a Fri, Sat, or Sunday, then decrement iso_week by 1. I didn't spend much time on this so check my thinking.

I asked ChatGPT this

Quote:

---

SQL Server….

I am trying to select the week number from a date. Our company counts the first Monday of the year as week 1 with any days before that as week 0.

So example for 2022 with expected result….

1/1/2022 - Sat - Week 0
1/2/2022 - Sun - Week 0
1/3/2022 - Mon - Week 1
etc.

1/1/2023 - Sun - Week 0
1/1/2023 - Mon - Week 1
etc.

---

Its answer:

Quote:

---

To select the week number from a date in SQL Server, you can use the `DATEPART` function and specify the `ww` datepart, which returns the week of the year (1-53) based on the ISO 8601 standard. To get the week number according to your company's definition, where the first Monday of the year is considered week 1 and any days before that are considered week 0, you can use the following query:

Quote:

---

SELECT
DATEPART(ww, [date]) -
CASE
WHEN DATEPART(dw, [date]) = 1 THEN 1
ELSE 0
END AS week_number
FROM mytable;

---

The `DATEPART(ww, [date])` function returns the week of the year for the given `[date]`, and the `CASE` statement checks if the day of the week for the given `[date]` is Monday (1), in which case it subtracts 1 from the week number, otherwise it returns the week number as is.

You can test this query with your examples by using the following `INSERT` and `SELECT` statements:

Quote:

---

-- Insert test data
INSERT INTO mytable (date) VALUES
('2022-01-01'), -- Sat - Week 0
('2022-01-02'), -- Sun - Week 0
('2022-01-03'), -- Mon - Week 1
('2023-01-01'), -- Sun - Week 0
('2023-01-02'); -- Mon - Week 1

-- Select the week number
SELECT
DATEPART(ww, [date]) -
CASE
WHEN DATEPART(dw, [date]) = 1 THEN 1
ELSE 0
END AS week_number
FROM mytable;

---

This should return the week numbers for the given dates as follows:

Quote:

---

2022-01-01, Week 0
2022-01-02, Week 0
2022-01-03, Week 1
2023-01-01, Week 0
2023-01-02, Week 1

---

You can adjust the query as needed to get the desired week numbers for your company's definition.

---

I haven't tried this yet but that is incredible. And a little scary. Thanks!

had this problem a while ago

I fixed it in PHP. Maybe this logic helps.

function getWeekNumber($datestring)
{ $date = strtotime($datestring);
$year = (int)date('Y',$date);
$startofyearday = strtotime($year."-01-01");

$weekstartofyear = (int)date('W', $startofyearday);
$daystartofyear = (int)date('w', $startofyearday);
$month = (int)date('n',$date);
$day_before = strtotime("tomorrow", $date);
$week = (int)date('W', $day_before);
if($weekstartofyear>1)
$week++;
if($week>52)
if($month == 1)
$week=1;

if($daystartofyear == 7)
$week--;
return $week;
}