Hi, all,
i just found a useful trick i thought someone else might be able to use...
As part of a presentation i'm preparing to introduce colleagues to CTEs,
i'm attempting to build a calendar (with output similar to the Unix 'cal'
command). (Please no spoilers - let me figure it out!)
As part of that, i've of course got to determine if it's a leap year. My
first attempt was something like this snippet:
with
config(year,month) AS(
select strftime("%Y"), strftime("%m")
),
daysPerMonth(month, days) as (
select 1, 31
union all
select 2,
case WHEN NOT c.year%4 AND c.year%100
THEN 29
ELSE CASE
WHEN NOT c.year%4 AND NOT c.year%100 AND NOT c.year%400
THEN 29
ELSE 28
END
END
...
(Not 100% sure that's right, but the replacement solution makes it a moot
point.)
Then it occurred to me that strftime() can tell us the day-of-year, which
answers that question for us much more tersely because December has a fixed
number of days:
...
daysPerMonth(month, days) as (
select 1, 31
union all
select 2,
CASE WHEN '366'=strftime("%j", c.year||"-12-31") THEN 29 ELSE 28 END
from config c
...
)
Note that string comparison is required, as +"2016" in sqlite does not
coerce a string to an integer like -"2016" does (i'm a bit surprised by
that, but expect it's an immutable compatibility constraint).
Similar reformulations could also work, taking care to cast the strftime
results to integers and performing a single math op:
select 2, 28 + (CAST(strftime("%j", c.year||"-12-31") AS INTEGER) % 365)
(i think that's about as terse as i can get it.)
So far my calendar CTE outputs:
[stephan at host:~/tmp]$ sqlite3 < cal.sql
2016|1|31
2016|2|29
2016|3|31
2016|4|30
2016|5|31
2016|6|30
2016|7|31
2016|8|31
2016|9|30
2016|10|31
2016|11|30
2016|12|31
Obviously still lots to do here. (Again, _please_ don't post spoilers for
calendar CTE solutions (in this thread)!)
Have fun!
--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
