Re: [sqlite] Date Selection

2008-06-12 Thread Federico Granata 
On Thu, Jun 12, 2008 at 5:50 AM, Harold Wood [EMAIL PROTECTED] wrote:

 Hello Igor
 the create table statement:

 CREATE TABLE Items
  (
  ID INT NOT NULL PRIMARY KEY ASC,
  SubCatId  INT NOT NULL,
  Description VARCHAR(60) NOT NULL,
  LastUnitPrice NUMERIC(6,2) DEFAULT 0.0,
  AvgUnitPrice NUMERIC(6,2) DEFAULT 0.0,
  MinUnitPrice NUMERIC(6,2) DEFAULT 0.0,
  LastPurchaseDate DATETIME,
  LastQtyPurchase  NUMERIC(6,2) DEFAULT 0.0,
  PurchaseUnit VARCHAR(20),
  NumTimesPurchased INT DEFAULT 0,
  NeedIt  BIT DEFAULT 0,
  FOREIGN KEY (SubCatId) REFERENCES Subcategory(ID)
  );

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

Re: [sqlite] Date Selection

2008-06-12 Thread Igor Tandetnik 
Harold Wood [EMAIL PROTECTED]
wrote in message news:[EMAIL PROTECTED]
 the create table statement:

 LastPurchaseDate DATETIME,

You seem to be under impression there's a dedicated DATETIME type in 
SQLite. This is not the case: it's just the nature of SQLite's manifest 
typing (http://sqlite.org/datatype3.html) that allows one to specify any 
odd identifier as a column type.

You have a choice of storing dates and times as strings (e.g. 
'2008-06-10'), as integer number of seconds since Unix epoch, or as 
floating point Julian day number. You manipulate these representations 
using built-in date/time functions:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Igor Tandetnik



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

Re: [sqlite] Date Selection

2008-06-12 Thread Harold Wood 
thanks.

--- On Thu, 6/12/08, Igor Tandetnik lt;[EMAIL PROTECTED]gt; wrote:

From: Igor Tandetnik lt;[EMAIL PROTECTED]gt;
Subject: Re: [sqlite] Date Selection
To: sqlite-users@sqlite.org
Date: Thursday, June 12, 2008, 7:51 AM

Harold Wood lt;[EMAIL PROTECTED]gt;
wrote in message news:[EMAIL PROTECTED]
gt; the create table statement:
gt;
gt; LastPurchaseDate DATETIME,

You seem to be under impression there's a dedicated DATETIME type in 
SQLite. This is not the case: it's just the nature of SQLite's manifest

typing (http://sqlite.org/datatype3.html) that allows one to specify any 
odd identifier as a column type.

You have a choice of storing dates and times as strings (e.g. 
'2008-06-10'), as integer number of seconds since Unix epoch, or as 
floating point Julian day number. You manipulate these representations 
using built-in date/time functions:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Igor Tandetnik



___
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

[sqlite] Date Selection

2008-06-11 Thread Harold Wood 
I have a table with a date column.nbsp; I want to select * from TableA where 
DateCol Between '2008-06-10' and '2008-06-11';nbsp; when i execute that query 
i get 0 records. when i remove the date selection i get all teh records.
nbsp;
what is the best way to query on date?
nbsp;
thanks

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

Re: [sqlite] Date Selection

2008-06-11 Thread Igor Tandetnik 
Harold Wood [EMAIL PROTECTED]
wrote in message news:[EMAIL PROTECTED]
 I have a table with a date column.nbsp; I want to select * from
 TableA where DateCol Between '2008-06-10' and '2008-06-11';nbsp;
 when i execute that query i get 0 records. when i remove the date
 selection i get all teh records. nbsp;
 what is the best way to query on date?

How do you store your dates? Show the output of this statement:

select DateCol, typeof(DateCol) from TableA limit 1;

Igor Tandetnik 



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

Re: [sqlite] Date Selection

2008-06-11 Thread Harold Wood 
Hello Igor
the create table statement:
nbsp;
CREATE TABLE Items
nbsp;nbsp;nbsp;nbsp; (
nbsp;nbsp;nbsp;nbsp; 
IDnbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;nbsp;
 INT NOT NULL PRIMARY KEY ASC,
nbsp;nbsp;nbsp;nbsp; SubCatIdnbsp;nbsp;INT NOT NULL,
nbsp;nbsp;nbsp;nbsp; Descriptionnbsp;VARCHAR(60) NOT NULL, 
nbsp;nbsp;nbsp;nbsp; LastUnitPricenbsp;NUMERIC(6,2) DEFAULT 0.0,
nbsp;nbsp;nbsp;nbsp; AvgUnitPricenbsp;NUMERIC(6,2) DEFAULT 0.0,
nbsp;nbsp;nbsp;nbsp; MinUnitPricenbsp;NUMERIC(6,2) DEFAULT 0.0,
nbsp;nbsp;nbsp;nbsp; LastPurchaseDatenbsp;DATETIME,
nbsp;nbsp;nbsp;nbsp; LastQtyPurchase nbsp;NUMERIC(6,2) DEFAULT 0.0,
nbsp;nbsp;nbsp;nbsp; PurchaseUnitnbsp;VARCHAR(20),
nbsp;nbsp;nbsp;nbsp; NumTimesPurchasednbsp;INT DEFAULT 0,
nbsp;nbsp;nbsp;nbsp; NeedItnbsp;nbsp;BIT DEFAULT 0,
nbsp;nbsp;nbsp;nbsp; FOREIGN KEY (SubCatId) REFERENCES Subcategory(ID)
nbsp;nbsp;nbsp;nbsp; );

--- On Wed, 6/11/08, Igor Tandetnik lt;[EMAIL PROTECTED]gt; wrote:

From: Igor Tandetnik lt;[EMAIL PROTECTED]gt;
Subject: Re: [sqlite] Date Selection
To: sqlite-users@sqlite.org
Date: Wednesday, June 11, 2008, 10:45 PM

Harold Wood lt;[EMAIL PROTECTED]gt;
wrote in message news:[EMAIL PROTECTED]
gt; I have a table with a date column.amp;nbsp; I want to select * from
gt; TableA where DateCol Between '2008-06-10' and
'2008-06-11';amp;nbsp;
gt; when i execute that query i get 0 records. when i remove the date
gt; selection i get all teh records. amp;nbsp;
gt; what is the best way to query on date?

How do you store your dates? Show the output of this statement:

select DateCol, typeof(DateCol) from TableA limit 1;

Igor Tandetnik 



___
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