:StartDate and :EndDate are NAMED PARAMETERS for when your application executes the statement (that is, they are substituted with the values you want for the StartDate and EndDate respectively.
I take it you want to compute YEARS MONTHS and DAYS between two dates: WITH RECURSIVE dates (StartDate, EndDate) as ( select '2004-02-02', '2018-08-03' ), yearC (dateY) AS ( SELECT date(StartDate, '+1 year') FROM dates WHERE date(StartDate, '+1 year') <= EndDate UNION ALL SELECT date(dateY, '+1 year') FROM yearC, dates WHERE date(dateY, '+1 year') <= EndDate ), years (dateY, years) as ( SELECT coalesce((SELECT max(dateY) FROM yearC), StartDate), coalesce((SELECT count(*) FROM yearC), 0) FROM dates ), monthC (dateM) as ( SELECT date(dateY, '+1 month') FROM years, dates WHERE date(dateY, '+1 month') <= EndDate UNION ALL SELECT date(dateM, '+1 month') FROM monthC, dates WHERE date(dateM, '+1 month') <= EndDate ), months (dateM, months) as ( SELECT coalesce((SELECT max(dateM) FROM monthC), dateY), coalesce((SELECT count(*) FROM monthC), 0) FROM years ), dayC (dateD) as ( SELECT date(dateM, '+1 day') FROM months, dates WHERE date(dateM, '+1 day') <= EndDate UNION ALL SELECT date(dateD, '+1 day') FROM dayC, dates WHERE date(dateD, '+1 day') <= EndDate ), days (dateD, days) as ( SELECT coalesce((SELECT max(dateD) FROM dayC), DateM), coalesce((SELECT count(*) FROM dayC), 0) FROM months ) SELECT StartDate, DateY, DateM, DateD, EndDate, years, months, days FROM dates, years, months, days; StartDate dateY dateM dateD EndDate years months days ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 2004-02-02 2018-02-02 2018-08-02 2018-08-03 2018-08-03 14 6 1 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál >Sent: Sunday, 5 August, 2018 02:08 >To: SQLite mailing list >Subject: Re: [sqlite] Using CTE with date comparison > >2018-08-05 0:18 GMT+02:00 Keith Medcalf <kmedc...@dessus.com>: >> >> WITH RECURSIVE >> dates(dateD) AS (VALUES(:StartDate) >> UNION ALL >> SELECT date(dateD, '+1 year') >> FROM dates >> WHERE date(dateD, '+1 year') <= :EndDate >> ) >> SELECT max(dateD), count(*) FROM dates; > >How do I interpret the ':StartDate' and ':EndDate'? > >Should I replace for example the ':StartDate' with '1983-07-11' like >this: > >sqlite> WITH RECURSIVE dates(dateD) AS (VALUES('1983-07-11') UNION >ALL >SELECT date(dateD, '+1 year') FROM dates WHERE date(dateD, '+1 year') ><= '1984-08-31' ) SELECT max(dateD), count(*) FROM dates; >1984-07-11|2 > >But this is ot what I want, because between 1983-07-11 and 1984-08-31 >there is exactly one whole year out there, namely: from 1983-07-11 to >1984-07-11. So the result value '2' above is wrong in the sense that >there is 1 year and not 2 years out there. > >The following SQL commands gives to me the right output, what I >desired: >sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1983-07-11','+1 >year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE >date(dateD, '+1 year') <= '1984-08-31' ) SELECT max(dateD), count(*) >FROM dates; >1984-07-11|1 >sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1984-11-01','+1 >year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE >date(dateD, '+1 year') <= '1986-01-15' ) SELECT max(dateD), count(*) >FROM dates; >1985-11-01|1 >sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('1986-01-16','+1 >year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE >date(dateD, '+1 year') <= '1999-07-16' ) SELECT max(dateD), count(*) >FROM dates; >1999-01-16|13 > >but not in the following case: >sqlite> WITH RECURSIVE dates(dateD) AS (VALUES(date('2000-02-01','+1 >year')) UNION ALL SELECT date(dateD, '+1 year') FROM dates WHERE >date(dateD, '+1 year') <= '2000-08-31' ) SELECT max(dateD), count(*) >FROM dates; >2001-02-01|1 > >because here in the last example there should be '0' and not '1' in >the output of that command. > >I am still trying to find the proper way to do this, what I desired, >if it is possible at all. > >-- >Best, Pali >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users