Re: [sqlite] Date Selection

2008-06-12 Thread Harold Wood 
thanks.

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

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

"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
___
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 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-11 Thread Harold Wood 
Hello Igor
the create table statement:

CREATE TABLE Items
 (
 
ID
 INT NOT NULL PRIMARY KEY ASC,
 SubCatIdINT NOT NULL,
 DescriptionVARCHAR(60) NOT NULL, 
 LastUnitPriceNUMERIC(6,2) DEFAULT 0.0,
 AvgUnitPriceNUMERIC(6,2) DEFAULT 0.0,
 MinUnitPriceNUMERIC(6,2) DEFAULT 0.0,
 LastPurchaseDateDATETIME,
 LastQtyPurchase NUMERIC(6,2) DEFAULT 0.0,
 PurchaseUnitVARCHAR(20),
 NumTimesPurchasedINT DEFAULT 0,
 NeedItBIT DEFAULT 0,
 FOREIGN KEY (SubCatId) REFERENCES Subcategory(ID)
 );

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

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

"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
___
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. I want to select * from
> TableA where DateCol Between '2008-06-10' and '2008-06-11';
> when i execute that query i get 0 records. when i remove the date
> selection i get all teh records. 
> 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] Date Selection

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

what is the best way to query on date?

thanks

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