SQLite: how to create a date from integer year and month components?

Question

I have a SQLite table with integer year and month columns. I'd like be able to 'cast' them as a date type for certain queries, but the simplest way I've found is long and unintuitive:

...
date(
    cast(year as text) || "-" ||
    substr('0' || cast(month as text), -2, 2) ||
    "-01" -- day of the month is irrelevant
    )
...

Is there a clearer, more concise and reusable way to do this?

Answer 1

First thing would be to remove that date function call as there is no date type in sqlite:

sqlite> select typeof(date('now'));
text

Second thing would be to remove that cast as the concatenation operator will implicitely convert your numbers to string:

sqlite> select 2011 || '-' || 1 || '-' || 1;
2011-1-1
sqlite> select typeof(2011 || '-' || 1 || '-' || 1);
text

That's all I can think of (other than removing the 3rd argument of the substr call), so your expression is sensibly shorter:

year || "-" || substr('0' || month, -2)  || '-01'