Look at the date functions, the file date.c is self explanatory and
lists the reference for the date type. The underlying type for a date
is a float, so that may be how you missed the date details.
P Kishor wrote:
On 6/4/07, John Stanton <[EMAIL PROTECTED]> wrote:
Sqlite does have a date format, it is physically a 64 bit floating point
number.
If that is the case, I would love to see the documentation on that. As
far as I can see, there are only TEXT, REAL, INTEGER, BLOB, NULL types
supported.
There are functions to transform in and out of that format to
present dates as required by the user. The Sqlite date format uses a
magib epoch which matches all of the major internaional date systems.
P Kishor wrote:
> There is no "DATE" format in SQLite. Dates are stored as strings. The
> only formats SQLite knows and understands are TEXT, REAL, INTEGER,
> BLOB, and NULL (see the link on datatypes). On the other hand, there
> are built-in functions that can act on your date strings and convert
> them back and forth, manipulate them, etc. Once again, see the link on
> working with dates on the SQLite wiki.
>
> On 6/4/07, Chris Fonnesbeck <[EMAIL PROTECTED]> wrote:
>
>> 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.
>>
>> Yet more strangeness:
>>
>>
>> sqlite> select date(First_Capture) from PIT_manatees limit 10;
>>
>>
>>
>>
>>
>>
>>
>> 1999-10-13
>>
>>
>> sqlite>
>>
>> Not sure what that is all about -- all the results are blank except
one,
>> ewven though every record is in exactly the same format.
>>
>> I'm totally perplexed by date handling in SQLite, so any help is most
>> appreciated.
>>
>> Thanks,
>> --
>> Christopher J. Fonnesbeck
>>
>> Population Ecologist, Marine Mammal Section
>> Fish & Wildlife Research Institute (FWC)
>> St. Petersburg, FL
>>
>> Adjunct Assistant Professor
>> Warnell School of Forestry and Natural Resources
>> University of Georgia
>> Athens, GA
>>
>> T: 727.235.5570
>> E: Chris dot Fonnesbeck at MyFWC dot com
>>
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
