Tuesday, August 4, 2009

Getting the week on sybase

I wanted to get the week number from sybase ASE. The requirement was simple:
  • Monday is the first day of a week ( a week is from mon - sun)
  • Even if 1st January was a Sunday, the week 26 Dec - 01 Jan would be called week 1 (and not week 53).
This is known as ISO standard 8601.

For sybase this was pretty simple (since the server was configured to use ISO 8601). This pretty much explains itself:

select datepart(cwk, convert(datetime,'2009/01/03',111)) as thisyear
, datepart(cwk, convert(datetime,'2008/12/28',111)) as lastyearlastweeklastday
, datepart(cwk, convert(datetime,'2008/12/29',111)) as thisyearfirstweekfirstday

This returns 1,52,1.