These aren't the same dates—and it's the non-parameterized ones you've got wrong.
Date(2014-01-01) calculates the arithmetic expression 2014 - 01 - 01, then constructs a Date from the resulting number 2012, which will get you something in 4707 BC.
Date('2014-01-01'), or Date(?) where the parameter is the string '2014-01-01', constructs the date you want, in 2014 AD.
You can see this more easily by just selecting dates directly:
>>> cur.execute('SELECT Date(2014-01-01), Date(?)', ['2014-01-01'])
>>> print(cur.fetchone())
('-4707-05-28', '2014-01-01')
Meanwhile:
What is the best way to pass a date variable into a SQL query?
Ideally, use actual date objects instead of strings. The sqlite3 library knows how to handle datetime.datetime and datetime.date. And don't call Date on the values, just compare them. (Yes, sqlite3 might then compare them as strings instead of dates, but the whole point of using ISO8601-like formats is that this always gives the same result… unless of course you have a bunch of dates from 4707 BC lying around.) So:
start = datetime.date(2014, 1, 1)
end = datetime.date(2015, 1, 1)
c.execute("""SELECT tweeterHash.* FROM tweeterHash, tweetDates WHERE
tweetDates.start > ? AND
tweetDates.end > ?""",
(start,end,))
And would this also mean that when I create the table, I would want: " start datetime, end datetime, "?
That would work, but I wouldn't do that. Python will convert date objects to ISO8601-format strings, but not convert back on SELECT, and SQLite will let you transparently compare those strings to the values returned by the Date function.
You could get the same effect with TEXT, but I believe you'd find it less confusing, DATETIME will set the column affinity to NUMERIC, which can confuse both humans and other tools when you're actually storing strings.
Or you could use the type DATE—which is just as meaningless to SQLite as DATETIME, but it can tell Python to transparently convert return values into datetime.date objects. See Default adapters and converters in the sqlite3 docs.
Also, if you haven't read Datatypes in SQLite Version 3 and SQLite and Python types, you really should; there are a lot of things that are both surprising (even—or maybe especially—if you've used other databases), and potentially very useful.
Meanwhile, if you think you're getting the "right" results from passing Date(2014-01-01) around, that means you've actually got a bunch of garbage values in your database. And there's no way to fix them, because the mistake isn't reversible. (After all, 2014-01-01 and 2015-01-02 are both 2012…) Hopefully you either don't need the old data, or can regenerate it. Otherwise, you'll need some kind of workaround that lets you deal with existing data as usefully as possible under the circumstances.
