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

Answer 1

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.