views:

298

answers:

1

I need to read the column value of the following query

SELECT strtime('%Y-%m', created_at) as field FROM table GROUP BY field

the type of column field is 3 (I assume it is blog), but I need string

How should I?

Updated

const char* sql = "SELECT CAST(strftime('%Y', created_at) as INTEGER) as year FROM table GROUP BY year

if (SQLITE_OK == sqlite3_prepare_v2([AppDelegate db], sql, -1, &queryhandle, NULL)){ while(SQLITE_ROW == sqlite3_step(queryhandle)){

   NSSTring* year  = [NSString stringWithFormat:@"%d",sqlite3_column_int(queryhandle, 0)];

this code gives me 3460 as year

(have checked cast as integer, and cast as varchar and sqlite3_column_text function)

+1  A: 

As long as created_at has a time string format as specified in the sqlite docs, it doesn't matter what storage type is used in a particular tuple (although you can find out using the typeof) function. The result of the strftime function (you aliased it as field) is either text or null.

CREATE TABLE t (x BLOB);
INSERT INTO t VALUES ("2010-03-29\x0"), ("2010-03-28");
SELECT strftime("%Y-%m", x), typeof(strftime("%Y-%m", x)), typeof(x) FROM t;
|null|text
2010-03|text|text
p00ya
have the same results in SQLite Manager, but can't get back valid data
se_pavel