declare @FirstPayDate datetimeset @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') ) aorder by a.Date
Date FirstPayDateOfCurrentQuarter----------------------- ----------------------------2010-07-27 00:00:00.000 2010-07-02 00:00:00.0002010-08-27 00:00:00.000 2010-07-02 00:00:00.0002010-10-01 00:00:00.000 2010-10-08 00:00:00.000(3 row(s) affected)