Select Date Based On Week

• Takamoto

  Valued Member

  Points: 52

  More actions

  November 28, 2007 at 3:01 pm

  #75820

  Scenario:

  ClosingWeek (table name)

  Date (column)

  2008-04-21

  2008-04-28

  2008-04-01

  2008-04-07

  2008-04-14

  Declare @date datetime

  Set @date = '2008-04-18'

  Need to select the date in table (Closingweek) where 04-18 falls under.

  So based on the above example 04-18 will fall under week 2008-04-14.

  04-23 will fall under 2008-04-21

  Hope this makes sense

  Any ideas on how to effectively select this date from the above table?

  Any information will be greatly appreciated.

• Antares686

  SSC Guru

  Points: 125444

  More actions

  November 28, 2007 at 3:20 pm

  #755168

  Look at DATEPART with the wk option.

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  November 28, 2007 at 3:46 pm

  #755175

  The function on this link should give you what you want.

  Start of Week Function:

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

  select

  Date,

  [Week stating Monday] =

  dateadd(dd,(datediff(dd,-53690,a.DATE)/7)*7,-53690)

  from

  MyTable a