On Sun, Aug 05, 2018 at 05:25:02AM -0600, Keith Medcalf wrote: > > :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 understand now. Thank you! > I take it you want to compute YEARS MONTHS and DAYS between two dates: Exactly that is what I want. > 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 Thank you very much! That's what I wanted, albeit with a small addition: I should get one day more. This is due the fact that when calculated a month, days which belongs to a month are as follows, e.g. for a month which is 31 days long: 1., 2., 3., ..., 30., 31. Some where in the WITH clause above I want to put '+1 day' in the command out there. I am going to study this command which I want to use in a Trigger. Thank you very much again for the solution! -- Best, Pali _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
