locked
Bug in SSRS DateInterval.WeekOfYear RRS feed

  • Question

  • The function DateInterval.WeekOfYear   computes week 53 for 2018  which does not exist.

    my full expression is run on 7 of january 2019

    = (CStr(format(dateadd("d",-7,now),"yy"))+"W"+right(CStr(100+DatePart(DateInterval.WeekOfYear, dateadd("d",-7,now), vbMonday, vbFirstFourDays)),2))

    Results in 18W53

    I know it is useless to ask Microsoft to fix anything but keep guys in mind that timedate calculations from Microsoft are not reliable (just as anything they do)  :)

    i know there can be bugs but Microsoft is terrible at fixing them. Speak not from my emotion but experience. :)


    From Project Techcenter


    Monday, January 7, 2019 10:37 AM

Answers

  • The value returned by  is dependent on FirstDayOfWeekValue value which by default is set to Sun

    As such it takes Dec 30 as start of last week with just two days (30,31). This period is what is referred to as 2008 W 53

    If your system week starts with different date then set a value for FirstDayOfWeekValue accordingly and it will calculate Week No based on that

    For example,  

    If week starts on Tuesday then 30 and 31 will be part of 52nd week ( as 52nd week spans from 25th Dec to 31 Dec)  and there wont be week 53 for 2018

    you can check this by below expression

    =DatePart("ww",CDate("31 Dec 2018"),FirstDayOfWeek.Tuesday,FirstWeekOfYear.FirstFourDays)

    which gives 52 and

    =DatePart("ww",CDate("01 Jan 2019"),FirstDayOfWeek.Tuesday,FirstWeekOfYear.FirstFourDays)

    gives 1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Mitarai Queen Tuesday, January 8, 2019 5:48 AM
    • Marked as answer by Maxim Ivashkov Thursday, January 10, 2019 2:34 PM
    Monday, January 7, 2019 1:20 PM

All replies

  • Max

    What version are you using?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, January 7, 2019 11:33 AM
  • The value returned by  is dependent on FirstDayOfWeekValue value which by default is set to Sun

    As such it takes Dec 30 as start of last week with just two days (30,31). This period is what is referred to as 2008 W 53

    If your system week starts with different date then set a value for FirstDayOfWeekValue accordingly and it will calculate Week No based on that

    For example,  

    If week starts on Tuesday then 30 and 31 will be part of 52nd week ( as 52nd week spans from 25th Dec to 31 Dec)  and there wont be week 53 for 2018

    you can check this by below expression

    =DatePart("ww",CDate("31 Dec 2018"),FirstDayOfWeek.Tuesday,FirstWeekOfYear.FirstFourDays)

    which gives 52 and

    =DatePart("ww",CDate("01 Jan 2019"),FirstDayOfWeek.Tuesday,FirstWeekOfYear.FirstFourDays)

    gives 1


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Mitarai Queen Tuesday, January 8, 2019 5:48 AM
    • Marked as answer by Maxim Ivashkov Thursday, January 10, 2019 2:34 PM
    Monday, January 7, 2019 1:20 PM
  • HI , 

    i will try your sintax with FirstDayOfWeek.Tuesday, i fill like there is one day shift for the parameter FirstDayOfWeek required, so europeans should use FirstDayOfWeek.Tuesday  for week number calculations. 

    however in the Netherlands it should be Monday and the formula computes week 53 (vbMonday parameter)

    (CStr(format(dateadd("d",-7,now),"yy"))+"W"+right(CStr(100+DatePart(DateInterval.WeekOfYear, dateadd("d",-7,now), vbMonday, vbFirstFourDays)),2))


    From Project Techcenter

    Thursday, January 10, 2019 2:33 PM