Sqllite Strftime Not Reading Column Value

SQLLite strftime not reading column value

This "how do i convert 8/1/2007 to 08/01/2017 within a query string" won't help, since that is not a valid sqlite time string. And if MySql is not an option (which it is not for me), you would have to do the "complicated string functions". Which can be fairly straight forward once they're broken down.

The year is easy, it is substr(adate,-12,4) assuming the time component only has one digit for hour (-13 otherwise). And if you really only want the year (based on '%Y') then bob's your uncle.

The month, likewise, is easy. Exploiting CAST which will convert text to an integer until the first non-numeric character, and using printf to 0 pad, this printf('%02i',CAST (adate as int)) will give the month.

The day is a little more typing, because you need the part of the string after the first '/'. This printf('%02i',CAST (substr(adate,instr(adate,'/') + 1) as int)) will do the trick.

All that's left is to concat them all together with some - in between, and voilà, it's a valid sqlite date.

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

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.

SQLite strftime() returning null with integers

You can use strftime() but you have to add the 'unixepoch' modifier:

strftime('%Y', date / 1000, 'unixepoch')

so your date / 1000 is recognized as the number of seconds since 1970-01-01.

From Date And Time Functions:

The "unixepoch" modifier (11) only works if it immediately follows a
timestring in the DDDDDDDDDD format. This modifier causes the
DDDDDDDDDD to be interpreted not as a Julian day number as it normally
would be, but as Unix Time - the number of seconds since 1970.

strftime function in SQLite does not work

strftime isn't broken, it just doesn't understand the format of the datestamps in your database.

SQLite version 3.7.9 2011-11-01 00:52:41
sqlite> create table dates (id int, d datetime);
sqlite> insert into dates (id,d) values (1, date('now'));
sqlite> insert into dates (id,d) values (2, 'bad date');
sqlite> insert into dates (id,d) values (3, NULL);
sqlite> insert into dates (id,d) values (4, datetime('now'));
sqlite> select * from dates;
1|2012-09-23
2|bad date
3|
4|2012-09-23 04:31:36
sqlite> select id, strftime('%m', d) from dates;
1|09
2|
3|
4|09

---

Related Topics