Syntax for date range SQL query in OpenOffice Base

Question

I'm trying to SELECT based on a date in OpenOffice Base:

SELECT *
  FROM OrderTbl
  WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007#

I've tried a number of variations, but nothing is working. What is the correct syntax?

Answer 1

This should work:

SELECT * 
FROM OrderTbl
WHERE OrdDate BETWEEN '2007-01-01' AND '2007-01-31'

Answer 2

use below Query SELECT * FROM OrderTbl WHERE OrdDate BETWEEN cast(convert(varchar,'1/1/2007',101) as datetime) AND cast((varchar,'1/31/2007',101) as datetime)

Answer 3

How I could do if I want to the user give me values for my range? Because this code doesn't work: SELECT * FROM ORDER_TABLE WHERE ORDER_DATE BETWEEN ? AND ? with this code works only ask me the first value, never ask me for the second.

Someone could you help me? please!