Using < and > in a select query

[Grynch]

I am trying to write a select query to recieve results between two date ranges

i have got these lines in my cfquery tag (example):

and ii.date_time > 10/04/2005
and ii.date_time < 20/04/2005

when i comment the last line out, i get the results. but if i comment the
first line out, I get nothing back (there are records between those dates).

What i think may be the problem is that when i type '<' inside the cfquery
tag, it turns blue. It may think i am trying to open a tag?

Can anyone see what the problem may be?

Thanks.

[Adam Cameron]

> I am trying to write a select query to recieve results between two date ranges

BETWEEN is the keyword here.

Use:
where column between someDate and someOtherDate

> i have got these lines in my cfquery tag (example):
> and ii.date_time > 10/04/2005
> and ii.date_time < 20/04/2005

Your syntax above is bung: you can't use dates in that format. What you're
saying is like 10 divided by 4 divided by 2005. Format the dates AS dates
(exactly how that is depends on your DB engine).

> What i think may be the problem is that when i type '<' inside the cfquery
> tag, it turns blue. It may think i am trying to open a tag?

That's just your IDE getting confused with its syntax highlighting. It's
got no bearing on the problem.

--

Adam

[Grynch]

Thanks Adam,

I will give that a shot.

I am using a MS Access DB driver, and i have the date_time column data type
set as 'Date/Time' . I have tried using quotes around the date, which it does
not like. I get a data type mismatch error. If i am selecting a date, it seems
to work if i use the date without quotes, which i'm not actually sure how it
works.

[The ScareCrow]

ii.date_time Between #CreateODBCDate("10/04/2005")# And #CreateODBCDate("20/04/2005")#

Ken