Help with the Week Number Case statement

1

Hi Peeps,

I have a case statement as below which returns me Week CW00 for week ending date '2017-01-01', I need to get CW52 instead of it.

      ,CASE 

            WHEN DATEPART(wk,pdth.week_ending_date) < 10 THEN 'CW0' + CONVERT(NVARCHAR(2),DATEPART(wk,pdth.week_ending_date)-1)

            ELSE 'CW' + CONVERT(NVARCHAR(2),DATEPART(wk,pdth.week_ending_date)-2)

       END                                                  AS week_number

any ideas on how i can i achieve this?

Thanks

2

Instead of "datepart(wk,.....)" use "datepart(iso_week,.....)"

3

It returns me CW50 instead of CW52 using ISO_WEEK

4

Probably because you subtract 2 from when week number you receive:

5

You could probably replace the whole case statement with this:

,'CW'+right('0'+datename(iso_week,pdth.week_ending_date),2) as week_number
6

Thanks but it ends up in error the multipart identifier pdth.week_ending_date could not be bound

7

Thanks

I sorted it out.
,'CW' + right('0' + CAST(datename(iso_week,pdth.week_ending_date) AS varchar(2)) ,2) AS week_number