Date to week in RB 6.1

[henx]

Hi,

I'm working on a report where I would like to convert a date to a week, is this possible to do? I have tried searching for solutions on this but as far as i can tell the only way around it is to write a function on the (MS) sql server that does the conversion and then print this valut in the report. Is there really no support for this in report builder 6.1? I also want to print the day of the week which I have found the function for but I have not been able to locate a way of printing the week.

Does any one here know if there is something that I'm missing or is writing a function in sql to do this the only option?

[Jeff Luther]

In my 25 years (or more) cache of TD samples and tests I have kept over the years I found a sample from 2010 that look to do what you want: Return the # of the week (it's 36 for this week of 6 March) given a date. I see the code is in TD v5.2 and I just tried it with v6.3 so it should work for you.

OH, OOPS... Sorry but I just see your title says "RB" -- that's ReportBuilder and what I gave you was a way to do it in TD. Possibile ways:

1) One easy way is to use the code I provided in TD and pass that week# as another Input item to your report.
or
2) I see in the sample I provided that the algorithm for getting the week # given a date is:

Code: Select all

( SalDateWeekBegin( dtDate ) - SalDateWeekBegin( SalDateConstruct( SalDateYear( SalDateWeekBegin( dtDate ) + 3 ), 1, 4, 0, 0, 0 ) ) ) / 7 + 1

Since I don't see an RB function like DateWeekBegin that could be used to write your own function, I would say my option 1) above would definitely be easier.

[henx]

Thanks Jeff, I appreciate the assist, was getting "slightly" annoyed when I had been working on trying to figure this out for a while.

Will report back if I get it to work...