SQL day of week question

• Carl Mueller

  Newbie

  Points: 2

  More actions

  April 20, 2004 at 12:51 pm

  #162405

  I need to write a query to get the prior Sunday of the current week I'm in. Is there a built in function in SQL or will I need to write one?

  i.e. Today we are in week 17 and the prior Sunday is 4/18/2004

  

  Thanks!

• K. Brian Kelley

  SSC Guru

  Points: 114704

  More actions

  April 20, 2004 at 1:25 pm

  #503537

  If Sunday is defined as the start of the week, something like this works:

  SELECT DATEADD(dd, -1 * (select datepart(dw, GETDATE()) -1), GETDATE())

  You can use CONVERT() to get it into the format you want. This is, of course, assuming if the day is Sunday you want that day, not a week prior.

  K. Brian Kelley
  @kbriankelley

• Carl Mueller

  Newbie

  Points: 2

  More actions

  April 20, 2004 at 2:29 pm

  #503544

  Thanks Brian,

  That worked very nicely. I used the field name (which was a date) in place of the getdate() within a select while building a view.

  Thanks again!