CODEWITHSUNDEEP
sqlitestrftime
Eme Emertana
Eme Emertana

Reputation: 571

strftime function in SQLite does not work

Hi, I am using the following query but it does not work, 

SELECT strftime('%m',Since) AS Month FROM Dates

Type of Since column is DATETIME, but the result is shown an empty Month column without any error.

Upvotes: 3

Views: 12718

Answers (2)

James Bond
James Bond

Reputation: 2986

If you have a column of INTEGER type with unix timestamp, you can convert it to the time value supported by SQLite's Date And Time Functions

SELECT STRFTIME('%Y.%m.%d', DATETIME(`column_name`, 'unixepoch')) FROM `info`;

Upvotes: 1

j.w.r
j.w.r

Reputation: 4239

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

Upvotes: 4

Related Questions