Re: [sqlite] datetime question

[Keith Medcalf]

> create table test( Date datetime);

Datetime is not a data type and therefore has numeric affinity.

> insert test now();

sqlite> select now();
Error: no such function: now

What is function now and what does it return?

> select date+2 as bbb,date-12 as cc from test;
> the result is

> 2014,2000

>From this I can surmise that your "now()" is the number 2012.

> I wanna know how to add any day use sql command

First you need to store an application specific representation of the datetime 
value in the "date" column.  Then you apply the function to that value that 
achieves the result you desire.  For example, if you store an MJD value in 
"Date" then you add or subtract fractional days.  If you store a unix-epoch 
value, then adding/subtracting 86400 moves forward and back a day.  There are 
builtin functions to help with some of this.

You might want to refer to:

http://www.sqlite.org/datatype3.html
and
http://www.sqlite.org/lang_datefunc.html

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of YAN HONG YE
> Sent: Wednesday, 22 August, 2012 20:14
> To: sqlite-users@sqlite.org
> Subject: [sqlite] datetime question
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime question

[Simon Slavin]

On 23 Aug 2012, at 3:14am, YAN HONG YE  wrote:

> create table test( Date datetime);
> insert test now();
> select date+2 as bbb,date-12 as cc from test;
> the result is 
> 2014,2000
> I wanna know how to add any day use sql command

There is no field type 'datetime'.  Dates are often expressed as TEXT.

Do not use 'Date' as a fieldname because the word 'Date' is used by SQLite for 
a keyword.

sqlite> CREATE TABLE test (thisDate TEXT);
sqlite> INSERT INTO test (thisDate) VALUES (date('now'));
sqlite> SELECT * from test;
2012-08-23
sqlite> SELECT date(thisDate,'+2 day'), date(thisDate,'-12 day') FROM test;
2012-08-25|2012-08-11

For documentation see this page:



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] datetime question

[YAN HONG YE]

create table test( Date datetime);
insert test now();
select date+2 as bbb,date-12 as cc from test;
the result is 
2014,2000
I wanna know how to add any day use sql command
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users