File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and the fly likes the between clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "the between clause" Watch "the between clause" New topic

the between clause

Mahesh Lohi
Ranch Hand

Joined: Jun 22, 2009
Posts: 150
Hi all,

My am getting an error for the following mysql syntax


right syntax to use near 'mydate BETWEEN 20091226 AND 20091226 GROUP BY item_name' at line 1

Thank you in advance
Praveen Rajendran

Joined: Dec 25, 2009
Posts: 21

I guess the problem is you missed an AND between the where clauses salesman and mydate.

select item_name,SUM(qty),SUM(total_amt) from invoice where salesman=? mydate BETWEEN ? AND ? GROUP BY item_name"

I guess the below code would work.. let me know..

select item_name,SUM(qty),SUM(total_amt) from invoice where salesman=? and mydate BETWEEN ? AND ? GROUP BY item_name

Thanks & Regards,
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3697

For purposes of creating more-standard SQL queries I recommend you replace "X BETWEEN Y AND Z" syntax with "X > Y AND X < Z". At the very least it gives you better control over inclusive (<=,>=) searches as well as creates code that works better on more systems. Least of which, I find the syntax of both to be similar. Anyone have reasons for strongly recommend ever using the keyword BETWEEN?

My Blog: Down Home Country Coding with Scott Selikoff
Vijitha Kumara

Joined: Mar 24, 2008
Posts: 3775

Behaviour of BETWEEN is vendor specific.It might pick only the values in between the boundary values provided or includes both boundary values as well or .... BETWEEN

[How to ask questions] [Twitter]
Mahesh Lohi
Ranch Hand

Joined: Jun 22, 2009
Posts: 150
Thank you all for those replies.

I agree. Here's the link:
subject: the between clause
Similar Threads
SCJP2 Question - Compiler Error Vs. Run-time Error
Error ORA-00937
LEFT JOIN problem. 2 tables, include null
MS Access and Date
CriteriaAPI -how to bin Expressions like hour(...)