Search Clause: where DATE >= 'YYYY-MM-DD' FAILS

[duncang]

Any suggestions as to why the following search clause fails to return
any rows ?

select P.Description, D.StartDate, D.DueDate,D.PromToRelActual
from ProjectWBS P, ProjectWBSDetail D
where ProjectCode = '011'
and D.ID=P.ID
and D.PromToRelActual >= '2006-11-01'
and D.PromToRelActual < '2006-12-01'
order by D.PromToRelActual

My workaround was the following:

select P.Description, D.StartDate, D.DueDate,D.PromToRelActual
from ProjectWBS P, ProjectWBSDetail D
where ProjectCode = '011'
and D.ID=P.ID
and cast(D.PromToRelActual as varchar(10)) >= '2006-11-01'
and cast(D.PromToRelActual as varchar(10)) < '2006-12-01'
order by D.PromToRelActual

Table definitions are:

create table ProjectWBS
   (ID              numeric          NOT NULL,
    ParentID        numeric          ,
    IndexNo         int              NOT NULL,
    Type            int              NOT NULL,
    Description     varchar(64)      NOT NULL,
    ProjectCode     varchar(12)      NOT NULL,
    Status          int              DEFAULT 0,
    TStamp          timestamp        DEFAULT CURRENT_TIMESTAMP ,    
    PRIMARY KEY (ID)
)

create table ProjectWBSDetail
   (ID                  numeric         NOT NULL,
    Description         long varchar    ,
    Release             varchar(64),
    StartDate           date            ,
    DueDate             date            ,
    ChiefProg           varchar(64)     ,
    Owner               varchar(64)     ,
    DevEstimate         float           DEFAULT 0.0 NOT NULL,
    Actual              float           DEFAULT 0.0 NOT NULL,
    Priority            int             DEFAULT 0 NOT NULL,
    Complexity          int             DEFAULT 2 NOT NULL,
    ReqGatherStatus     varchar(12)    ,
    ReqGatherPlan       date           ,
    ReqGatherActual     date           ,
    ReqWalkThroStatus   varchar(12)    ,
    ReqWalkThroPlan     date         ,
    ReqWalkThroActual   date         ,
    DesignStatus        varchar(12)    ,
    DesignPlan          date             ,
    DesignActual        date             ,
    DesignInspStatus    varchar(12)    ,
    DesignInspPlan      date          ,
    DesignInspActual    date          ,
    BuildStatus         varchar(12)    ,
    BuildPlan           date             ,
    BuildActual         date             ,
    BuildInspStatus     varchar(12)    ,
    BuildInspPlan       date             ,
    BuildInspActual     date             ,
    PromToRelStatus     varchar(12)    ,
    PromToRelPlan       date             ,
    PromToRelActual     date             ,
    TStamp               timestamp        DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY (ID)
);
alter table ProjectWBSDetail add CONSTRAINT ProjectWBSDetailFK01
FOREIGN KEY (ID) REFERENCES ProjectWBS(ID);


---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to mckoidb-unsubscribe@...

[Stefaan A Eeckels-2]

Hi Duncan,

On Mon, 06 Nov 2006 23:49:28 +1300
Duncan Groenewald <duncangroenewald@...> wrote:

> Any suggestions as to why the following search clause fails to return
> any rows ?

The syntax is as follows:

 select P.Description, D.StartDate, D.DueDate,D.PromToRelActual
 from ProjectWBS P, ProjectWBSDetail D
 where ProjectCode = '011'
 and D.ID=P.ID
 and D.PromToRelActual >= DATE '2006-11-01'
 and D.PromToRelActual < DATE '2006-12-01'
 order by D.PromToRelActual

Take care,

--
Stefaan
--
As complexity rises, precise statements lose meaning,
and meaningful statements lose precision. -- Lotfi Zadeh


---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to mckoidb-unsubscribe@...

[duncang]

Thanks...

Stefaan A Eeckels wrote:

> Hi Duncan,
>
> On Mon, 06 Nov 2006 23:49:28 +1300
> Duncan Groenewald <duncangroenewald@...> wrote:
>
>  
>> Any suggestions as to why the following search clause fails to return
>> any rows ?
>>    
>
> The syntax is as follows:
>
>  select P.Description, D.StartDate, D.DueDate,D.PromToRelActual
>  from ProjectWBS P, ProjectWBSDetail D
>  where ProjectCode = '011'
>  and D.ID=P.ID
>  and D.PromToRelActual >= DATE '2006-11-01'
>  and D.PromToRelActual < DATE '2006-12-01'
>  order by D.PromToRelActual
>
> Take care,
>
>  

---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to mckoidb-unsubscribe@...

[Stefaan A Eeckels-2]

On Tue, 07 Nov 2006 18:01:25 +1300
Duncan Groenewald <duncangroenewald@...> wrote:

> Thanks...

You're very welcome.

--
Stefaan
--
As complexity rises, precise statements lose meaning,
and meaningful statements lose precision. -- Lotfi Zadeh


---------------------------------------------------------------
Mckoi SQL Database mailing list  http://www.mckoi.com/database/
To unsubscribe, send a message to mckoidb-unsubscribe@...