Thursday, January 29, 2009

Calculate Date for a given week in an year.

How to calculate the date for a given week number in an year.

For E.G. If you have the week number "23" to transalate that to a date like "July-17-2009" I used this technique.

Take the week number multiply that by 7 to get the number of days since the beginning of the year and then construct the date.

In MySql - there is a date function called "makedate" which takes the year and the number of days as an parameter

"makedate(year(logintime),week(logintime) * 7)" This will return a date like "2009-07-21"

In DB2 to do the same thing.

"(DATE(((week_iso(a.logintime)-1)* 7)+ 2) + (year(a.logintime) -1) years)"

Take precuation to assume your week beginning with respect to your business logic, some businesses the week begins on sunday and some for monday and so on...

In MySql there are system variables to set the default start of week...

No comments:

Post a Comment