Calculate week from date in Report Builder 1.5

Discussion forum about all things Report Builder (all versions).
chris064

Calculate week from date in Report Builder 1.5

Post by chris064 » 28 Feb 2013, 15:43

Hi,

we are bound to the quite old Report Builder 1.5.1-PTF6 because our software is based on that old Centura Version.

Now I need to show the calendar week of a given date on a report. As there is now build in function (no such picture format for DateToStrPicture) I tried to write a function myself. I found a formula for Excel. The problem is that that formula converts a date to a number to calculate with it. But that is also something I cannot figure out how to achieve in report builder. I can only find a date to string conversion.

Can anyone give me a hint how to proceed? I am sure there must be a way to get the week from date.

Regards, Chris

MSchmidt
Germany
Posts: 299
Joined: 03 Jul 2017, 09:28
Location: Germany

Re: Calculate week from date in Report Builder 1.5

Post by MSchmidt » 28 Feb 2013, 22:45

what kind of DB do you use?

if MS SQL, you can use SQL Server functions like isoweek:

We use the following function on a SQL Server:
(this function calculates the week no according to the rules for Germany
In US the rules are different!

Code: Select all

CREATE FUNCTION dbo.kw(@dtDate as DATETIME) RETURNS INT WITH RETURNS NULL ON NULL INPUT 

AS 
BEGIN 

DECLARE @intISOWeekdayNumber INT 
DECLARE @dtThisThursday DATETIME 
DECLARE @dtFirstOfThisThursdaysYear DATETIME 
DECLARE @intISOWeekdayNumberOfFirstOfThisThursdaysYear INT 
DECLARE @dtFirstThursdayOfYear DATETIME 
DECLARE @intISOWeekNumber INT 
  
   SET @intISOWeekdayNumber = (((DATEPART(dw, @dtDate) - 1) + (@@DATEFIRST - 1)) % 7) + 1 

   SET @dtThisThursday = DATEADD(d,(4 - @intISOWeekdayNumber),@dtDate) 

   SET @dtFirstOfThisThursdaysYear = CAST(CAST(YEAR(@dtThisThursday) AS CHAR(4)) + '-01-01' AS DATETIME) 
 
   SET @intISOWeekdayNumberOfFirstOfThisThursdaysYear = (((DATEPART(dw, @dtFirstOfThisThursdaysYear) - 1) + (@@DATEFIRST - 1)) % 7) + 1 

   IF (@intISOWeekdayNumberOfFirstOfThisThursdaysYear in (1,2,3,4)) 
      SET @dtFirstThursdayOfYear = DATEADD(d,(4 - @intISOWeekdayNumberOfFirstOfThisThursdaysYear),@dtFirstOfThisThursdaysYear) 
   ELSE 
      SET @dtFirstThursdayOfYear = DATEADD(d,(4 - @intISOWeekdayNumberOfFirstOfThisThursdaysYear + 7),@dtFirstOfThisThursdaysYear) 

   SET @intISOWeekNumber = DATEDIFF(d,@dtFirstThursdayOfYear,@dtThisThursday)/7+1 
    
   RETURN @intISOWeekNumber 
 
END ;
If you use this function inside a normal select you can map it to an input item of your report.

select kw(DATEFIELD) ,... from XY into ....


Regards
M.Schmidt

chris064

Re: Calculate week from date in Report Builder 1.5

Post by chris064 » 01 Mar 2013, 08:53

Hi M. Scmidt,

thanks for your extensive answer.

The back end is an Oracle database. That indeed includes functions for calender weeks even both US and ISO.

However our reports are filled by the third party application we market and support. So I am bound to the input items and variables that application hands over along with the build in functions of the report builder 1.5.

The Excel function I found is:
=TRUNC((A1-DATE(YEAR(A1+3-MOD(A1-2,7)),1,MOD(A1-2,7)-9))/7)

Is there anyway to translate this functions into the report builder syntax?

Regards, Chris

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests