2

Here is a great way of how to get day of week for a date, Deterministic scalar function to get day of week for a date.

Now, could anyone help me to create a deterministic scalar function to get week of year for a date please? Thanks.

Community
  • 1
  • 1
Irawan Soetomo
  • 1,315
  • 14
  • 35
  • 1
    There are many, many questions on this site about extracting specific date attributes in SQL and the easiest solution is usually to create a [calendar table](http://stackoverflow.com/questions/2459260/calendar-table-week-number-of-month). – Pondlife Jul 11 '12 at 12:50
  • Thanks @Pondlife, your link has inspired me. I am going to answer my own question based on that. – Irawan Soetomo Jul 11 '12 at 13:08
  • possible duplicate of [Getting week number off a date in MS SQL Server 2005?](http://stackoverflow.com/questions/348880/getting-week-number-off-a-date-in-ms-sql-server-2005) – bummi May 13 '14 at 21:58

1 Answers1

3

This works deterministically, I can use it as a computed column.

datediff(week, dateadd(year, datediff(year, 0, @DateValue), 0), @DateValue) + 1

Test code:

;
with 
Dates(DateValue) as 
(
    select cast('2000-01-01' as date)
    union all 
    select dateadd(day, 1, DateValue) from Dates where DateValue < '2050-01-01'
)
select 
    year(DateValue) * 10000 + month(DateValue) * 100 + day(DateValue) as DateKey, DateValue,        
    datediff(day, dateadd(week, datediff(week, 0, DateValue), 0), DateValue) + 2 as DayOfWeek,
    datediff(week, dateadd(month, datediff(month, 0, DateValue), 0), DateValue) + 1 as WeekOfMonth,
    datediff(week, dateadd(year, datediff(year, 0, DateValue), 0), DateValue) + 1 as WeekOfYear
    from Dates option (maxrecursion 0)
Irawan Soetomo
  • 1,315
  • 14
  • 35