• o103452 - Monday, December 18, 2017 2:56 PM

    Lynn Pettis - Monday, April 2, 2012 9:34 AM

    For a SQL based solution, you can try this, but please note that it won't work for billing dates that start on the 29th, 30th, or 31st of the month.declare @BillDate datetime;declare @StartDay int;set @StartDay = 21; -- Beginning day of billing cycle, this code will not work properly for billing dates starting 29 - 31set @BillDate = cast('20120401' as datetime); -- Test dateselect @BillDate;with e2 ( N) as (select 1 union all select 1),e10 ( N) as (select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1),e20 ( N) as (select row_number() over (order by (select null)) from e2 a cross join e10 b)--select N from e20;select convert(varchar(10),dateadd(dd, @StartDay - 1, dateadd(mm, datediff(mm, 0, dateadd(dd, -N * @StartDay,@BillDate)), 0)),101) + ' - ' + convert(varchar(10),dateadd(dd, -1, dateadd(mm, 1, dateadd(dd, @StartDay - 1, dateadd(mm, datediff(mm, 0, dateadd(dd, -N * @StartDay,@BillDate)), 0)))),101)from e20

    I know this is a really old thread, but was wondering if you've came up with a solution that works for any start date (including 29th, 30th, and 31st). Thank you

    Nope, haven't had any reason to work on this.