Get weeks in SQL

Question

I am sure that I missed something very basic, but I can not wrap it around me after a request and search for an hour.

I have an existing datetable if that helps. It is filled with the dates of past years and updated every night through a stored procedure that is launched by the task on the server.

What I need to do is pull Monday-Sunday of the week, so I can correctly join them in the tracking table to get a weekly hit chart. I don’t want to write 52 gradually larger sentences and combine them for obvious reasons, and I don’t like the idea of ​​cursors.

I have already done this for months and days, but the nature of the weeks confuses me for some reason or is different in nature.

Any thoughts on how to automate the process without cursors or a huge selection / pool? I’ll follow the cursor and throw it on the table at night, if absolutely necessary, but I hope not.

FYI my desired format at the end of this will be:

[Week number] | [StartDate] | [EndDate] 

For every week

Answer 1

I might not agree with what you want, but it looks like you want this type:

- eg. the number of records grouped by week, for 2009

 SELECT DATEPART(wk, DateField) AS WeekNumber, COUNT(*) AS HitsForWeek FROM SomeTable WHERE DateField >= '20090101' AND DateField < '20100101' GROUP BY DATEPART(wk, DateField) 

Answer 2

One thing added is AdaTheDev's answer is right, but by default it will give you Sunday to Saturday of the week. If you want from Monday to Sunday, you need to complete

SET DATEFIRST 1

to set the first day of the week on Monday (1). You can ask @@ DateFirst to find out what your settings are - the default is 7 (Sunday) in the US English setting.

Answer 3

I also found the weeks, and below is the code that I use to convert the date to a group of dates of the week. The following are examples that will return weekends or weekends on a Monday or a week without supporting a support table. You can also configure them to use on Saturday and Sunday, if that is what you want to use.

 --Returns the weekending (Friday) for a particular day SELECT DATEADD(day, -DATEPART(weekday,yourDate), yourDate)+6 --Returns the week commencing (Monday) for a particular day SELECT DATEADD(day, -DATEPART(weekday,yourDate)+2, yourDate) 

Happy coding :)

Answer 4

You can use datediff(day, '19800107', yourDate) / 7 (where January 7, 1980 is known Monday), so you don’t have to worry about when the system thinks the week has started. In addition, this mechanism is very easy to adapt to different weeks, without considering the value @@DATEFIRST , just select the famous Sunday or the famous Thursday or something else.