How to determine week of quarter

• karthur

  Old Hand

  Points: 399

  More actions

  August 24, 2009 at 10:26 am

  #209817

  Can someone help me with code that will give me the week number of each week of a quarter based on a date. For instance for April through June, there are 14 weeks.

  If I have an order date of 4/03/09, I need to return 'week 1'. If I have an order date of of 6/25/09, I need to return 'week 13'.

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  August 24, 2009 at 10:44 am

  #1043763

  select

  DT,

  WeekOfQuarter=(datediff(dd,dateadd(QQ,datediff(QQ,0,DT),0),DT)/7)+1

  from

  ( -- Test Data

  select DT = convert(datetime,'20090403') union all

  select DT = convert(datetime,'20090625')

  ) a

  Results:

  DT WeekOfQuarter

  ------------------------------------------------------ -------------

  2009-04-03 00:00:00.000 1

  2009-06-25 00:00:00.000 13

  (2 row(s) affected)