Week of Month given week of year

• chris-322173

  Valued Member

  Points: 63

  More actions

  June 29, 2009 at 10:19 am

  #73228

  I have a field that gives me the week of the year (pre defined fiscal periods) and I am trying to break it down to periods within a fiscal month. For example April I have 14,15,16,17 and I am trying to get 1,2,3,4...any ideas?

  example:

  select nbrweek from calendar where fiscalmonth = 4 and fiscalyear = 2009

  would return the 4 rows denominated as 14,15,16,17, I want 1,2,3,4

  Thanks!

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  June 29, 2009 at 10:35 am

  #1016973

  Ok here is an example of some code, it doesn't look up from a table but I think the formula is what you will be interested in.

  DECLARE @Year INT

  DECLARE @Month INT

  DECLARE @DateTime DATETIME

  SELECT @Year = 2009, @Month = 04

  SELECT @DateTime = CAST(@Year as VARCHAR) + '-01-01'

  SELECT

  @Datetime,

  DATEADD(wk,14,@DateTime),

  DATEPART(day,(DATEADD(wk,14,@DateTime))) / 7,

  DATEPART(day,(DATEADD(wk,15,@DateTime))) / 7,

  DATEPART(day,(DATEADD(wk,16,@DateTime))) / 7,

  DATEPART(day,(DATEADD(wk,17,@DateTime))) / 7

  ----------------------------------------------
  Try to learn something about everything and everything about something. - Thomas Henry Huxley

  :w00t:
  Posting Best Practices[/url]
  Numbers / Tally Tables[/url]

  SQL-4-Life

• chris-322173

  Valued Member

  Points: 63

  More actions

  June 29, 2009 at 12:48 pm

  #1017134

  awesome, thanks for the help!