开发者

Conversion of date function in sqlite

开发者 https://www.devze.com 2023-02-17 01:12 出处:网络
Trying to convert the date format \"2011-03-25 15:00:00.0\" to \"2011-03-25\" in sqlite and getting deep troubles.

Trying to convert the date format "2011-03-25 15:00:00.0" to "2011-03-25" in sqlite and getting deep troubles.

When I run this line, it works fine: sqlite> select strftime("%Y-%m-%d",'now'); 2011-03-16

But when I run 开发者_高级运维this line, it loses out : sqlite> select strftime("%Y-%m-%d",'date_start') from test;

What am I doing wrong in this?

Sagos


Remove the quotes from date_start

~ e$ sqlite3
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table test (dt);
sqlite> insert into test values (22222222);
sqlite> select strftime("%Y-%m-%d",dt) from test;
5613-14-26
sqlite> insert into test values (2222222);
sqlite> select strftime("%Y-%m-%d",dt) from test;
5613-14-26
1372-02-19
sqlite> 

sqlite> select substr('2011-03-22 08:00:00.0',1,19);
2011-03-22 08:00:00

sqlite> insert into test values ('2011-03-22 08:00:00.0');
sqlite> select strftime("%Y-%m-%d",substr(dt,1,19)) from test;
5613-14-26
1372-02-19
2011-03-22


sqlite> select distinct date_start from test;

        2011-03-22 08:00:00.0

        2011-03-22 09:00:00.0
0

精彩评论

暂无评论...
验证码 换一张
取 消