Inconsistent Datatypes and Bind Variable "00" Not Declared Error 1

[Hillary]

The formula is...

Select Buyer, Purc_Order_ID, Line_No, Part_ID, Purc_Order_Line.Desired_Recv_Date, Purc_Order_Line.Last_Received_Date, Order_Qty, Total_Received_Qty
From Purchase_Order, Purc_Order_Line
Where Purc_Order_Line.Desired_Recv_Date > 12/20/2002 and Purc_Order_Line.Desired_Recv_Date < 1/1/2003
Order By Buyer, Purc_Order_ID, Line_No

I am getting an Inconsistent Datatypes Error in my formula Where Purc_Order_Line.Desired_Recv_Date > 12/20/2002 and Purc_Order_Line.Desired_Recv_Date < 1/1/2003.

The datatype for Purc_Order_Line.Desired_Recv_Date is datetime so I added the time into the formula...

Where Purc_Order_Line.Desired_Recv_Date > 12/20/2002 12:00:00AM and Purc_Order_Line.Desired_Recv_Date < 1/1/2003 12:00:00AM.

When I add the time I get Bind Variable &quot;00&quot; Not Declared.

Can anyone help with the date range?

Thanks,
Hillary

 

[r937]

try putting your date strings into single quotes

e.g. Where Purc_Order_Line.Desired_Recv_Date > '12/20/2002'

rudy

 

[Hillary]

It returns &quot;Not a valid month&quot;

 

[r937]

try ISO format

Where Purc_Order_Line.Desired_Recv_Date > '2002-12-20'

 

[Hillary]

It returns &quot;literal does not match format string&quot;. Is there an easier way to query a Date Between besides > Date and < Date?

Thanks for your help!

Hillary

 

[r937]

perhaps ask in the appropriate forum

microsoft sql server forum183

microsoft access forum701

this is ansi sql

rudy

 

[Hillary]

Last question!

I am pretty new to sql so please excuse my ignorance.

We are running on ORACLE. I am using SQL Plus Worksheet to extract data so I don't think the other forums are appropriate. Why is this forum not appropriate? Shouldn't a vaild SQL statment work in any of these?

Hillary

 

[r937]

you would think so, but valid ansi sql doesn't work in every database

there are 14 different forums for oracle, just use the search box at the top of this page, make sure Find a Forum is checked

for your query, try

Where Purc_Order_Line.Desired_Recv_Date
> TO_DATE('12/20/2002','MM/DD/YYYY')

 

[tlbroadbent]

Hillary,

See faq220-1073 - &quot;What is ANSI SQL? Why This Forum?&quot; for at least a partial explanation of why you should be in an Oracle forum rather than the ANSI SQL forum.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[AME]

A Core SQL-99 compliant statement would use the DATE data type and compare it with DATE literals (rather then string literals).

That would give the following statement:
Select Buyer, Purc_Order_ID, Line_No, Part_ID, Purc_Order_Line.Desired_Recv_Date, Purc_Order_Line.Last_Received_Date, Order_Qty, Total_Received_Qty
From Purchase_Order, Purc_Order_Line
Where Purc_Order_Line.Desired_Recv_Date > DATE '2002-12-20'
and Purc_Order_Line.Desired_Recv_Date < DATE '2003-01-01'
Order By Buyer, Purc_Order_ID, Line_No

The SQL standard (for example SQL-99) does not allow string literals to be compared with DATE (or TIME or TIMESTAMP) values.