Creating a week_ref number to pass into a stored procedure

Question

Currently one of the reports reads the month_ref which is created like this:

((DATEPART(yy, month_date) - 1200) * 100) + DATEPART(mm, month_date) AS month_ref,
month_date, DATENAME(MONTH, month_date) + ' ' + DATENAME(YEAR, month_date) AS month_name

I would like to create something like a week_ref?

The month ref uniqely identifies the month for the year. When its passed into a particular stored procedure, the sp will filter the results based on the month ref.

I am now wanting to create a weekly report - (mon - sun) and need to uniquely identify the week_ref so i can filter the results using the sp.

How can i do this?

I think you should explain a bit about what a `month_ref` is and what you expect a `week_ref` to be and perhaps add some sample dates and what the `week_ref` value should be for those dates. — Mikael Eriksson, Mar 19 '12 at 11:09

t-clausen.dk · Accepted Answer · 2012-03-21T09:10:13.847

3

This will give you a unique number for each week from monday to sunday and it doesn't depend on datefirst.

SELECT datediff(day, 0, month_date) / 7 week_ref FROM <table>

This will return an integer always as tested here:

SELECT SQL_VARIANT_PROPERTY(datediff(day, 0, getdate()) / 7, 'BaseType')

Returns:

int

edited Mar 21 '12 at 09:10

answered Mar 19 '12 at 11:30

t-clausen.dk

43,517
12
59
92

This is actually very neat, because 1/1/1900 was a monday. Very cool. Just a question, won't it be saver to use `convert(int, datediff(day, 0, month_date) / 7) week_ref` to ensure that the result is an int? – Jaques Mar 19 '12 at 11:35
1

The reason why I used just the week Datepart function is because it is easier in the future to know which week of the year the report is for, and you can compare it to previous years for the same week. – Jaques Mar 19 '12 at 11:41
@Jaques that will be an unnessasary convert. The query will already return an integer as shown in the additional code – t-clausen.dk Mar 19 '12 at 11:46
Cool, still probably the best way that I saw to get a unique Mon-Sun Week number back. – Jaques Mar 19 '12 at 11:47
Whats the best to reverse the week_ref back to the original date? – SOLDIER-OF-FORTUNE Mar 21 '12 at 14:34

MatBailie · Answer 2 · 2012-03-19T11:52:29.333

1

Although I'd consider this technique very dirty, for months it does work. This is because there are always 12 months in a year.

For weeks it's both dirty and flawed. Some years have more weeks than others, because some weeks span more than one year.

I would recommend instead just using DATEDIFF().

Depending on the day-of-week that you want your weeks to start on, you need to do a little bit of calculation...

  - Sun as start of week = DATEDIFF(WEEK, -1, yourDate  )  
  - Mon as start of week = DATEDIFF(WEEK, -1, yourDate-1)  
  - Tue as start of week = DATEDIFF(WEEK, -1, yourDate-2)  
  - Wed as start of week = DATEDIFF(WEEK, -1, yourDate-3)  
  - Thu as start of week = DATEDIFF(WEEK, -1, yourDate-4)  
  - Fri as start of week = DATEDIFF(WEEK, -1, yourDate-5)  
  - Sat as start of week = DATEDIFF(WEEK, -1, yourDate-6)

I also recommend using this for your month calculations. DATEDIFF(MONTH, <a base date>, <your date>), the current one is really clunky.

EDIT Examples to turn the values above back into DATETIME

  - Sun as start of week = DATEADD(WEEK, DATEDIFF(WEEK, -1, yourDate  ), -1  )
  - Mon as start of week = DATEADD(WEEK, DATEDIFF(WEEK, -1, yourDate-1), -1+1)
  - Tue as start of week = DATEADD(WEEK, DATEDIFF(WEEK, -1, yourDate-2), -1+2)
  - Wed as start of week = DATEADD(WEEK, DATEDIFF(WEEK, -1, yourDate-3), -1+3)
  - Thu as start of week = DATEADD(WEEK, DATEDIFF(WEEK, -1, yourDate-4), -1+4)
  - Fri as start of week = DATEADD(WEEK, DATEDIFF(WEEK, -1, yourDate-5), -1+5)
  - Sat as start of week = DATEADD(WEEK, DATEDIFF(WEEK, -1, yourDate-6), -1+6)

edited Mar 19 '12 at 11:52

answered Mar 19 '12 at 11:17

MatBailie

83,401
18
103
137

Maybe this adds some more infomation: http://stackoverflow.com/questions/9741584/displaying-reporting-weeks/9743127#9743127 – SOLDIER-OF-FORTUNE Mar 19 '12 at 11:44
So I could check of the GET DATE() is between start and end date then date diff?? – SOLDIER-OF-FORTUNE Mar 19 '12 at 11:49
@sp-1986 - Unfortunately, `DATEDIFF()` and `DATEADD()` don't take `DATEFIRST` into account. So you're forced to do it manually. Using `DATEDIFF(DAY)` as in another answer here, of `DATEDIFF(WEEK)` but with an offset, as here. I will, however, edit to add how to convert the result back into a `DATETIME`. – MatBailie Mar 19 '12 at 11:50
Re: `DATEDIFF()` and `DATEADD()` don't take `DATEFIRST` into account. – DATEADD doesn't need to, as far as I understand. It just adds multiples of 7 days essentialy, doesn't it? – Andriy M Mar 19 '12 at 12:48
@AndriyM - Yes, the `DATE(WEEK, DATEDIFF(WEEK))` snippet seems correct to me. What do you see that may be in error? – MatBailie Mar 19 '12 at 13:28
I was confused by the last argument's values, thought (mistakenly) they represented the number of weeks to add. Sorry. – Andriy M Mar 19 '12 at 15:12
DATEDIFF(WEEK, -1, yourDate-1).. could be DATEDIFF(WEEK, 0, yourDate-1) with exactly the same result. – t-clausen.dk Mar 21 '12 at 09:17

score 0 · Answer 3 · answered Mar 19 '12 at 11:15

0

Use

Select DATEPART(wk, GetDate())   --In your case month_date and not GetDate()

to get the week of the year back, and then you can use any formula you like to create a week_ref, but from what I can see from your edit, this whould the only thing that you need. However, you would change your month_ref to something like this.

((DATEPART(yy, month_date) - 1200) * 10000) + (DATEPART(mm, month_date) * 100) + DATEPART(wk, month_date) AS month_ref

Then you don't have to add an extra column

answered Mar 19 '12 at 11:15

Jaques

2,215
1
18
35

Just something that you should keep in mind. If `month_date` can be any date of the week, you will have a problem with the `DATEPART(wk, GetDate())` function at the beginning and end of the year, as weeks overflow to the next year, and it is possible to have 53 weeks in a physical year. – Jaques Mar 19 '12 at 11:28

score 0 · Answer 4 · edited May 23 '17 at 12:27

Use a calendar table with (at least) 3 columns: base_date, month_ref and week_ref. Then to get the month and week references (whatever those are) for a certain date, you can query them directly from the calendar table without using hard-to-read nested functions or other 'non-obvious' code:

select week_ref, month_ref
from dbo.calendar
where base_date = @my_date

You haven't explained what week_ref and month_ref actually mean, but if they are just numbers then a calendar table has the significant advantage that it can support mutliple different definitions. For example, you might want to support both the ISO week number and your own, internal week number scheme. You can also easily change values in the table if your logic changes, without changing any code.

As a general observation, a calendar table is usually a better solution than inline functions, especially if your requirements involve reporting.

Creating a week_ref number to pass into a stored procedure

4 Answers4