declare @FirstPayDate datetime

set @FirstPayDate= '20100101'

select

*,

FirstPayDateOfCurrentQuarter =

dateadd(dd,(datediff(dd,@FirstPayDate,dateadd(dd,13,

dateadd(qq,datediff(qq,@FirstPayDate,Date),@FirstPayDate)))/14)*14,@FirstPayDate)

from

(

select Date = convert(datetime,'20100727')union all

select Date = convert(datetime,'20100827')union all

select Date = convert(datetime,'20101001')

) a

order by

a.Date

Results:

Date FirstPayDateOfCurrentQuarter

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

2010-07-27 00:00:00.000 2010-07-02 00:00:00.000

2010-08-27 00:00:00.000 2010-07-02 00:00:00.000

2010-10-01 00:00:00.000 2010-10-08 00:00:00.000

(3 row(s) affected)