FIND MONDAY OF WEEK 14

[LMCRYER]

Using CR 9, trying to find the first day and last day of a week based on the weeknumber (provided by the system).

I've tried datepart, datediff -- getting really weird data.
Does anyone have a formula already built for this?


Example = week 1 (jan 1 through jan 7 ) for 2004,
the first day (jan 1) = Wednesday, 01/01/2004.




LMC

 

[fallsr]

I had a similar need - I needed the first Monday of every month.

I used a formula from the BusinessObjects Knowledge Base article number c2006781. I think you will find what you need there.

Thanks,
Bob

 

[synapsevampire]

Understand that weeknumbers are NOT standardized, as one might assume, so make sure that you understand how it's being stored.

Anyway, try this:

dateadd("ww",0,cdate(2004,1,1)) & " - "& dateadd("ww",1,cdate(2004,1,1))-1

That give you the first and last dates for week 1.

Change it to the following for week 14:

dateadd("ww",13,cdate(2004,1,1)) & " - "& dateadd("ww",14,cdate(2004,1,1))-1

Obviously you can change the 13 and 14 to your weeknumber-1 and your weekenumber respectively.

-k

 

[synapsevampire]

I should have referenced the big kid approach as well, which is to use a Periods table, I have a FAQ on creating one complete with code for SQL Server:

http://www.tek-tips.com/faqs.cfm?fid=4532

-k

 

[LMCRYER]

Wow, that was fast! Thanks, it worked! (yea, I wanted to make a table, but my data's in oracle and we dont have a way to make the table in there (controlled instance from corporate)

Thanks!

Lisa

LMC