Date to week in RB 6.1

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

Date to week in RB 6.1

Post by henx » 09 Mar 2016, 17:34

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
Site Admin
Site Admin
United States of America
Posts: 2370
Joined: 04 Mar 2017, 18:34
Location: Palm Springs, California

Re: Date to week in RB 6.1

Post by Jeff Luther » 09 Mar 2016, 22:12

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.
You do not have the required permissions to view the files attached to this post.
Jeff Luther @ PC Design
Palm Springs, California

henx

Re: Date to week in RB 6.1

Post by henx » 10 Mar 2016, 08:26

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...

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests