How to use strftime() function in sqlite for extracting Year where datetime is in a different format than what would normally work

Question

While learning SQL in my course, I have used the function YEAR(Start_Time) AS Year

Start_Time is a column header. The date time example is in this format: 26/12/2017 09:00:00 YEAR() function works in SQL.

However, when I went on SQLite (on DB Browser), the function is not working. I used the strftime('%Y', 'Start_Time'). I understand that strftime goes from left to right. Does anyone know what would be the equivalent function to YEAR() in sqlite with the date time format given above?

In SQL:

YEAR(Start_Time) AS Year

Answer:

2017

In sqlite:

YEAR(Start_Time)

Answer:

function not recognized

strftime('%Y', 'Start_time')

Answer:

function not recognized

Does this answer your question? [SQLLite strftime not reading column value](https://stackoverflow.com/questions/60673844/sqllite-strftime-not-reading-column-value) — DinoCoderSaurus, Sep 01 '20 at 19:25

Thomas G · Accepted Answer · 2020-09-01T20:34:16.340

SQlite is pretty different than other RDBMS : It doesn't have a date/time type. You can only store dates as numbers or text.

The function strftime() is able to extract date/time info from a date stored in a text column, but it only works if you respect the ISO8601 notation: YYYY-MM-DD HH:MM:SS.SSS , which is not the case with your date.

If you want to extract the Year from it using strftime() , then you need to convert your date first

For your case, if you convert only the date part and not the time part, that works too.

Lets go:

SELECT  Start_Time, 
        (substr(Start_Time,7,4) || '-' || substr(Start_Time,4,2) || '-' || substr(Start_Time,1,2)) as dateconverted,
        strftime('%Y', (substr(Start_Time,7,4) || '-' || substr(Start_Time,4,2) || '-' || substr(Start_Time,1,2)) ) as year
FROM test;

Results

Start_Time              dateconverted     year
26/12/2017 09:00:00     2017-12-26        2017

If you want to avoid this mess, you just have to store your dates/times in the right format from the start, there's no other workaround.

score 1 · Answer 2 · edited Sep 02 '20 at 19:17

1

The way to do it it's your third choice. strftime('%Y', 'time string'). Make sure that time string it's a string in one of the accepted formats. You may see: (https://sqlite.org/lang_datefunc.html).

edited Sep 02 '20 at 19:17

David Brossard

13,584
6
55
88

answered Sep 01 '20 at 20:37

Erick

301
3
12

How to use strftime() function in sqlite for extracting Year where datetime is in a different format than what would normally work

2 Answers2

Linked