On 13 May 2016, at 3:56pm, Jonathan Moules <jonathan-lists at lightpear.com> wrote:
> Sorry, maybe we're crossing wires, but I'm not sure to what you're > referring. How is defining the type as DATE impeding my attempt to get a > value of 0 for non-existent rows? > Ryan's response with a CTE seems to probably be what I want (not had the > opportunity to test it yet - CTE's are entirely new to me), but looking at it > quickly now I see that the type is actually NUMERIC there rather than my DATE > - perhaps that's to what you're referring. Yes, that's it. You are specifying DATE as your type. SQLite understands that as meaning NUMERIC. You then put values into the columns which look like 2016-01-14 It's perfectly understandable that some part of your setup parses the string '2016-01-14', tries to make a number out of it, and comes up with a result of '2016', since that's where the number stops. Simon.
