Getting week number off a date in MS SQL Server 20

Question

Is it possible to create an sql statement that selects the week number (NOT the day of week - or the day number in a week). I'm creating a view to select this extra information along with a couple of other fields and thus can not use a stored procedure. I'm aware that it's possible to create a UDF to do the trick, but if at all possible i'd rather only have to add a view to this database, than both a view and a function.

Any ideas? Also where i come from, the week starts monday and week 1 is the first week of the year with atleast 4 days.

Related:

How do I calculate the week number given a date?

Answer 1

Looks like datepart will get you part of the way there, but you'll have to adjust to get your correct week number, based on the day of week of Jan 1 of the given year. I'm not familiar enough with T-SQL to do that, but it should be possible. Pity there isn't a mode argument as in MySQL

Answer 2

You need the ISO week. From http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510, here's an implementation:

drop function dbo.F_ISO_WEEK_OF_YEAR
go
create function dbo.F_ISO_WEEK_OF_YEAR
    (
    @Date   datetime
    )
returns     int
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin

declare @WeekOfYear     int

select
    -- Compute week of year as (days since start of year/7)+1
    -- Division by 7 gives whole weeks since start of year.
    -- Adding 1 starts week number at 1, instead of zero.
    @WeekOfYear =
    (datediff(dd,
    -- Case finds start of year
    case
    when    NextYrStart <= @date
    then    NextYrStart
    when    CurrYrStart <= @date
    then    CurrYrStart
    else    PriorYrStart
    end,@date)/7)+1
from
    (
    select
        -- First day of first week of prior year
        PriorYrStart =
        dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
        -- First day of first week of current year
        CurrYrStart =
        dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
        -- First day of first week of next year
        NextYrStart =
        dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
    from
        (
        select
            --Find Jan 4 for the year of the input date
            Jan4    = 
            dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
        ) aa
    ) a

return @WeekOfYear

end
go