CODEWITHSUNDEEP
sqlitedatedatetimecastingtype-conversion
Lars Blumberg
Lars Blumberg

Reputation: 21461

When casting a datetime as date, why does SQLite3 only return the year and not the full date?

I know that you can cast a datetime to date using the date() function:

sqlite> select date('2000-01-01 10:00:00');
2000-01-01

But why does SQLite3's cast expression such as in

sqlite> select cast('2000-01-01 10:00:00' as date);
2000

only return the year?

Even using an explicit datetime() setup solely returns the year:

sqlite> select cast(datetime('2000-01-01 10:00:00') as date);
2000

Or:

sqlite> select cast(datetime('now') as date);
2019

Looking at Postgresql, it resolves both properly:

postgresql> select date('2000-01-01 10:00:00');
2000-01-01

postgresql> select cast('2000-01-01 10:00:00' as date);
2000-01-01

What's the technical explanation for SQLite3's – to me unexpected – behavior?

Upvotes: 2

Views: 1420

Answers (1)

forpas
forpas

Reputation: 164214

For SQLite there is no Date datatype. As mentioned in their documentation here: https://www.sqlite.org/datatype3.html

What you use as Date is actually TEXT.

You can check that:

select typeof(datetime('now'));

returns:

text

And:

select typeof(cast(datetime('now') as date));

returns:

integer

So the result of cast('2000-01-01 10:00:00' as date) is an integer and it's the same integer that you get by:

select '2000-01-01 10:00:00' + 0

when SQLite implicitly converts '2000-01-01 10:00:00' to 2000 in order to use it in a mathematical operation.
In the case of dates it happens to be the numeric value of the year, but in general SQLite returns the longest substring of the TEXT, starting from the 1st character, that can be represented as an integer.
So for '2000-01-01 10:00:00' it's the substring until the 1st -, which is the year.

Upvotes: 4

Related Questions