SQl queries searching by date range
I have a table in an Access 2007 database. All fields are of type text. Can the following be done using the where clause? If so how?
- SELECT * from Table1 WHERE (ColumnDate is between 26th and 19th of march 2010)
- SELECT * from Table1 WHERE (ColumnAge is between 25 and 40)
The usual < <开发者_Python百科= operators don't seem to work.
Thanks,
SELECT * from Table1 WHERE (CDATE(ColumnDate) BETWEEN #03/26/2010# AND #03/19/2010#)
SELECT * from Table1 WHERE (CINT(ColumnAge) between 25 and 40)
Dates are represented in Access between #
symbols in #MM/DD/YYYY#
.
You should really be storing the date as a date field :)
SELECT * from Table1 WHERE ColumnDate between '2010-03-26' and '2010-03-19'
SELECT * from Table1 WHERE ColumnAge between 25 and 40
I don't use Access, so YMMV.
Try converting ColumnDate
to actual date/time with CDate
function. Conversion to int can be done with CInt
, I guess.
I don't use Access, so it's just a common-sense guess.
Mr.David-W-Fenton is right saying that the first one SELECT * from Table1 WHERE ColumnDate between '2010-03-26' and '2010-03-19'
by Marcelo doesn't work, and wrong suggesting the cause. The clause is incorrect because of quotes around string representations of dates, the date format is pretty OK. So I'd assume
SELECT * from Table1 WHERE CDATE(ColumnDate) between #2010-03-26# and #2010-03-19#
as a proper solution.
精彩评论