Tsql DateAdd with recursion

Transact-SQL
1

I have an t-sql script to generate a ‘bill payment’ date based on the a contract bill ‘start date’.

When the date is on e.g. 31st of the month the ‘sql’ works until it reaches e.g. February – where the day becomes 28th and continues along that line.

(Please see & run the sample sql for example)

I understand what’s happening – but im looking for a t-sql guru to come up with a creative way to help generate the ‘correct schedule’. i.e. one that reflects the last day of the month.

Any help would be most welcomed

Kind regards

Dan

--===========================================
--temp table for DateAdd example
--===========================================
DECLARE @dateadd TABLE (
ID INT,
term INT,
BillDate DATE
)

INSERT INTO @dateadd

SELECT 1
, 12
, '2018-12-31 00:00:00.000'

--===========================================
--generate bill table
--===========================================
;WITH bill AS (
SELECT ID=ID
, RepaymentNo=1 --set PaymentNo
, term
, BillDate as BillDate
FROM @dateadd

UNION ALL

SELECT	  ID
		, RepaymentNo + 1				as PaymentNo
		, term 
		, DateAdd(month,1,BillDate)
FROM bill
WHERE RepaymentNo < term)

--=========================================================
--notice how the 'BillDate' becomes the 28th after feb 2019
--=========================================================
select * FROM bill

2

Instead of
, DateAdd(month,1,BillDate)
use one of these
, DATEADD(mm,DATEDIFF(mm,0,BillDate)+2,-1)-- SQL 2012 or earlier
, EOMONTH(DATEADD(MONTH, 1, BillDate)) -- SQL 2014 or later

You may need to cast the columns as DATE or DATETIME in the anchor part of the query and the recursive part of the query if you get error messages about type mismatch between anchor and recursive parts.

3

EOMONTH is available as of 2012...

4

Thanks all - this solution only works when the bill date is endo of month. If i have a day like 22nd it will change that to end of month using the EOMONTH function.

Any other ideas?

see below

--===========================================
--temp table for DateAdd example
--===========================================
DECLARE @dateadd TABLE (
ID INT,
term INT,
BillDate DATE
)

INSERT INTO @dateadd

SELECT 1,12, '2018-12-31 00:00:00.000' union
SELECT 2,12, '2018-12-22 00:00:00.000'

--===========================================
--generate bill table
--===========================================
;WITH bill AS (
SELECT ID=ID
, RepaymentNo=1 --set PaymentNo
, term
, BillDate as BillDate
FROM @dateadd

UNION ALL

SELECT ID
, RepaymentNo + 1 as PaymentNo
, term
, DateAdd(month,1,BillDate)
--,EOMONTH(DATEADD(MONTH, 1, BillDate))
FROM bill
WHERE RepaymentNo < term)
--=========================================================
--notice how the 'BillDate' becomes the 28th after feb 2019
--=========================================================
select * FROM bill
order by 1,2

5 
--===========================================
 --temp table for DateAdd example
 --===========================================
 DECLARE @dateadd TABLE (
 ID INT,
 term INT,
 BillDate DATE
 )

INSERT INTO @dateadd

SELECT 1,12, '2018-12-31 00:00:00.000' union
 SELECT 2,12, '2018-12-22 00:00:00.000'

--===========================================
 --generate bill table
 --===========================================
  ;WITH bill AS (
 SELECT ID=ID
 , RepaymentNo=1 --set PaymentNo
 , term
 , BillDate as BillDate
 FROM @dateadd

UNION ALL

SELECT ID
 , RepaymentNo + 1 as PaymentNo
 , term
 , DateAdd(month,1,BillDate)
 --,EOMONTH(DATEADD(MONTH, 1, BillDate))
 FROM bill
 WHERE RepaymentNo < term)
 --=========================================================
 --notice how the 'BillDate' becomes the 28th after feb 2019
 --=========================================================
 select 
 *
 ,Convert(date, dateadd(s,-1,dateadd(month,1,dateadd(month,datediff(month,0,BillDate),0)))) as New_Bill_Date
  FROM bill
 order by 1,2
6

Here is one way to change the query to handle dates that are not the end of the month.
If this is a one-time query, it doesn't matter, but if you use this query often, it might be more efficient to use a Calendar table or Numbers/Tally table rather than recursive CTE.

--===========================================
--temp table for DateAdd example
--===========================================
DECLARE @dateadd TABLE
(
    ID INT,
    term INT,
    BillDate DATE
);

INSERT INTO @dateadd
SELECT 1,
       12,
       '2018-12-31 00:00:00.000'

DECLARE @billPayDayofMonth INT;
SELECT TOP (1) @billPayDayofMonth = DATEPART(DAY,BillDate) FROM @dateadd ORDER BY billDate ASC;


--===========================================
--generate bill table
--===========================================
;
WITH bill
AS (SELECT ID = ID,
           RepaymentNo = 1, --set PaymentNo
           term,
           CAST(BillDate AS DATE) AS BillDate
    FROM @dateadd
    UNION ALL
    SELECT ID,
           RepaymentNo + 1 AS PaymentNo,
           term,
           EOMONTH(DATEADD(MONTH, 1, BillDate))
    FROM bill
    WHERE RepaymentNo < term)
--=========================================================
--notice how the 'BillDate' becomes the 28th after feb 2019
--=========================================================
SELECT 
	b.ID, 
	b.RepaymentNo,
	b.term,
	BillDate = CASE 
		WHEN DAY(b.BillDate) > @billPayDayofMonth THEN 
			DATEFROMPARTS(YEAR(b.BillDate), MONTH(b.BillDate), @billPayDayofMonth)
		ELSE
			b.BillDate
		END
FROM bill b;
7

DECLARE @dateadd TABLE (
ID INT,
term INT,
BillDate DATE
)

INSERT INTO @dateadd

SELECT 1,12, '2018-12-31 00:00:00.000' union
SELECT 2,12, '2018-12-22 00:00:00.000'

--===========================================
--generate bill table
--===========================================
;WITH bill AS (
SELECT ID=ID
, RepaymentNo=1 --set PaymentNo
, term
, BillDate as BillDate
FROM @dateadd

UNION ALL

SELECT ID
, RepaymentNo + 1 as PaymentNo
, term
,DateAdd(month,1,BillDate)
--,EOMONTH(DATEADD(MONTH, 1, BillDate))
--,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,BillDate)+1,0))
FROM bill
WHERE RepaymentNo < term)
--=========================================================
--notice how the 'BillDate' becomes the 28th after feb 2019
--=========================================================
select ID,RepaymentNo,term,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,BillDate)+1,0)) as BillDa FROM bill
order by 1,2

8

Now I am confused - you were looking for a way to generate the last day of the month - but now you don't want to generate the last day of the month?

So what are the rules for generating the date?

If the payment date is the 28th of the month - do you want the payment date to be the 28th for all months? If the payment date is the 29th - do you want the payment date to be the 29th for all months except February (except on leap years?) - the same with the 30th - but for the 31st you want end of the month for all months?

Or is it something completely different?

9

hi

i tried to find a solution

please take a look

I hope it works
:slight_smile:
:slight_smile:

SQL 
DECLARE @dateadd TABLE 
  ( 
     id       INT, 
     term     INT, 
     billdate DATE 
  ); 

INSERT INTO @dateadd 
SELECT 1, 
       12, 
       '2018-12-31 00:00:00.000'; 

WITH bill 
     AS (SELECT ID = id, 
                RepaymentNo = 1,--set PaymentNo 
                term, 
                billdate 
         FROM   @dateadd 
         UNION ALL 
         SELECT id, 
                repaymentno + 1 AS PaymentNo, 
                term, 
                Cast (Dateadd(d, -1, Dateadd(m, Datediff(m, 0, Dateadd(month, 1, 
                                                billdate 
                                                )) + 1, 
                                     0)) 
                      AS DATE) 
         FROM   bill 
         WHERE  repaymentno < term) 
SELECT * 
FROM   bill 

go
Results

image