Re: [sqlite] extracting and comparing dates

[John Stanton]

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 -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]
-

Re: [sqlite] extracting and comparing dates

[John Stanton]

Chris Fonnesbeck 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 -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

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.


Store the dates in the internal Sqlite julian mode.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] extracting and comparing dates

[Rich Shepard]

On Mon, 4 Jun 2007, Chris Fonnesbeck 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 -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



Any help most appreciated.


Chris,

  I cannot tell you the answer with certainty, but two things jump out at me
about your data:

  1) They are not in -mm-dd format, but in -m(m)-d(d) format. I
always enter dates with a leading zero; e.g., 1997-06-17.

  2) Dates are strings (see  for
SQLite3 data storage classes). I put them between single quotes for
comparison purposes as text strings.

HTH,

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.|  Accelerator(TM)
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] extracting and comparing dates

[Samuel R. Neff]

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 -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]
-

[sqlite] extracting and comparing dates

[Chris Fonnesbeck]

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 -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

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.