-2

I would like to get last week and next week number from week number. How to get the next week number if it's December or the last week if it's January ?

I use ISO week numbers. Desired results:

enter image description here

jarlh
  • 42,561
  • 8
  • 45
  • 63
maria.l
  • 1
  • 1
  • 4
    Edit your question and provide sample data and desired results. – Gordon Linoff May 10 '17 at 11:49
  • Clearly next week number for december will always be 1 and previous week number for january will always be 53 – Andrey Korneyev May 10 '17 at 11:52
  • @AndyKorneyev your year has 53 weeks? :P Btw. January 1st 2017 and January 2nd 2017 was in week number 52. – waka May 10 '17 at 11:55
  • @AndyKorneyev, are you sure? ISO has one weeknumber definition, but many countries have their own versions. – jarlh May 10 '17 at 11:55
  • @waka, According to ISO 2017-01-01 was in week 52, but 2017-01-02 was in week 1. – jarlh May 10 '17 at 11:56
  • @jarlh Yes. That happens when you don't look carefully enough. My previous statement concerning January 2nd was wrong. :) – waka May 10 '17 at 12:04
  • To find previous week number, subtract 1 date from weekstart and return that date's week number. To find next week number, add 7 days etc. – jarlh May 10 '17 at 12:08

1 Answers1

0
select datepart(year,dateadd(day,-1,week_dt_start)) * 100 +
       datepart(week,dateadd(day,-1,week_dt_start)) as prev_week,
       datepart(year,dateadd(day,1,week_dt_end)) * 100 + 
       datepart(week,dateadd(day,1,week_dt_end)) as next_week
from tablename
jarlh
  • 42,561
  • 8
  • 45
  • 63
  • Thanks @jarlh for your answer but it's not working. I have an error message: Msg 195, Niveau 15, État 10, Ligne 2 'week' is not a recognized built-in function name. – maria.l May 10 '17 at 13:32
  • Too bad. When it comes to date/time too many products have their own way doing things... Well, wait a few seconds. – jarlh May 10 '17 at 13:47
  • **From review queue**: May I request you to please add some more context around your answer. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post. – RBT May 11 '17 at 01:29
  • @RBT, read my comment above and you'll understand. (I was also expecting OP to give more feedback...) – jarlh May 11 '17 at 06:53