How to find preceding 7th date of any given date.

• avdhut.k

  SSCrazy

  Points: 2834

  More actions

  September 6, 2013 at 12:44 am

  #276948

  Hi,

  My requirement is,I want preceding 7th date of any given date.

  For example,

  1)Declare @AsonDate As datetime = '2013-08-31'

  Then my output should be '2013-08-07'

  2)Declare @AsonDate As datetime = '2013-09-06'

  Then my output should be '2013-08-07'

  Kindly help me!!

  Thanks in Advance!!

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  September 6, 2013 at 12:53 am

  #1647925

  Here you go:

  DECLARE @AsonDate DATETIME = '2013-08-11';

  SELECT CASE WHEN DAY(@AsonDate) < 7

  THEN DATEADD(d,6,DATEADD(d,-(DAY(DATEADD(m,-1,@AsonDate))-1),DATEADD(m,-1,@AsonDate)))

  ELSE DATEADD(d,6,DATEADD(d,-(DAY(@AsonDate) - 1),@AsonDate))

  END;

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• avdhut.k

  SSCrazy

  Points: 2834

  More actions

  September 6, 2013 at 12:59 am

  #1647927

  Thanks!!