select last 30 days

• joshy mathew

  Old Hand

  Points: 361

  More actions

  January 22, 2008 at 5:03 am

  #119699

  hi Friends

  How can i select last 30 days excluding sundays

  Regards

  Josh

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  January 22, 2008 at 5:56 am

  #770434

  If you are just looking for a filter, it is pretty easy:

  SELECT

  *

  FROM

  MyTable

  WHERE

  MyDateField >= DATEADD(DAY,-30,GETDATE())

  AND DATEPART(WEEKDAY,MyDateField) <> 1 --Not a sunday

  If you are trying to actually produce a list of dates, let me know and I will send you a recursive CTE query for this.

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  January 22, 2008 at 6:11 am

  #770436

  Here would be a CTE example for a list of actual day values.

  ; WITH DateList (DateValue, Level)

  AS

  (

  SELECT CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101)), 0

  UNION ALL

  SELECT DATEADD(DAY,-1,DateValue), Level+1

  FROM DateList

  WHERE DATEADD(DAY,-1,DateValue) >= '1/1/2000'

  )

  SELECT

  DateValue

  FROM

  DateList

  WHERE

  DateValue >= DATEADD(DAY,-30,CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),101)))

  AND DATEPART(WEEKDAY,DateValue) <> 1

  OPTION (MAXRECURSION 30000)