How To Use Strftime() Function In Sqlite For Extracting Year Where Datetime Is In A Different Format Than What Would Normally Work

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(

Solution 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_Timedateconvertedyear26/12/201709:00:002017-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.

Solution 2:

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).