: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-
>[email protected]] 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 <[email protected]>:
>>
>> 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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
