CODEWITHSUNDEEP
sqlsqlite
Carina
Carina

Reputation: 13

How to extract date parts from text string in SQLite?

I have a column called event_time with the date & time in the following format: 2020-04-24 11:50:39 UTC

I'm trying to extract different parts of the date, such as year, using the following query:

SELECT event_time, strftime('%y',event_time) AS year

But the result is NULL. I've tried formatting the column as both TEXT and NUMERIC with no luck. Is the "UTC" preventing it from being recognized as a date? What is the best way to extract dates and times that I can work with?

Upvotes: 1

Views: 292

Answers (1)

forpas
forpas

Reputation: 164089

You must remove the UTC suffix and use %Y instead of %y as the format of strftime():

strftime('%Y', SUBSTR('2020-04-24 11:50:39 UTC', 1, 19)) AS year

or:

strftime('%Y', REPLACE('2020-04-24 11:50:39 UTC', 'UTC', ''))

return 2020.

See the demo.

Upvotes: 1

Related Questions