We use declared types of DATE, TIMESTAMP and DATETIME and store dates as
floating point using the Sqlite date conversion functions. The
applications get dates formatted as ISO8601 or according to the declared
locale. Functions do date artithmetic.
Samuel R. Neff wrote:
SQLite doesn't have any internal notion of a date, only numbers or strings.
You can choose to store dates as numbers and SQLite has some conversion
functions to help you work with dates. Personally I store dates as strings.
These are not properly formatted dates:
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15
should be
1997-06-17
1998-05-06
1997-06-24
1998-05-06
2000-03-15
this
sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
should be
sqlite> select First_Capture from PIT_manatees where
First_Capture<'2000-01-01'
and this
sqlite> select First_Capture from PIT_manatees where
First_Capture<date('2000-1-1')
works when storing dates as numbers, not text.
HTH,
Sam
-------------------------------------------
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Chris
Fonnesbeck
Sent: Monday, June 04, 2007 3:04 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] extracting and comparing dates
I'm at a complete loss about how to work with dates in SQLite. The
documentation doesnt seem to be helping me. I have a table with some date
fields, in the proper yyyy-mm-dd format:
sqlite> select First_Capture from PIT_manatees limit 5;
1997-6-17
1998-5-6
1997-6-24
1998-5-6
2000-3-15
however, it does not respond properly to comparisons to dates:
sqlite> select First_Capture from PIT_manatees where First_Capture<2000-1-1
limit 5;
sqlite>
this doesnt work either:
sqlite> select First_Capture from PIT_manatees where
First_Capture<date('2000-1-1') limit 5;
sqlite>
note that these columns are in the 'date' format.
How does one extract years, months, dates from tables in SQLite? The
'extract' function also seems not to work.
Any help most appreciated.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
