CODEWITHSUNDEEP
sqlsql-serverdateweek-number
BaronG
BaronG

Reputation: 80

Appending weeks to rolling date column

I have a daily rolling date column, [table].[Date], from 2020-06-27 to today.

I need to append a week number, with 2020-06-27 to 2020-07-03 as week 1 and so forth. Saturday to Friday defined as the week.

I have tried the following. Though this only defines the week of the year, so I would end up with overlaps when a year passes. Also, it does not define the first date as week 1.

datepart(week,[table].[Date]) as Week

Upvotes: 0

Views: 97

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Presumably, you are using SQL Server (based on the SSMS tag).

You can count the days and divide by 7:

select ceiling(datediff(day, '2020-06-26', t.date) / 7.0) as week_number

Or:

select 1 + floor(datediff(day, '2020-06-27', t.date) / 7.0) as week_number

If you don't want to hardcode the first date, you can use a window function:

select ceiling(datediff(day, min(t.date) over (), t.date) / 7.0) as week_number

Upvotes: 1

Related Questions