Hello,

I am trying to figure out how to do a query on a timestamp field for records
that are between two certain dates.

i.e. select start_date where start_date between date A and date B;

I have found thatworking with timestamps in Interbase is not easy and the
extract function doesn't look to friendly either.  Does anybody know of a
quick and dirty way of performing the above?

Thanks!

Randall

        This syntax works almost exactly as you've written it:

SELECT
  SOME_FIELD
FROM
  SOME_TABLE
WHERE
  START_DATE BETWEEN :StartDate AND :StopDate;

        Really?  What problem are you having?

        What's so hard about extract?

SELECT
  EXTRACT(YEAR FROM START_DATE), START_DATE
FROM
  SOME_TABLE

        HTH,

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

change where condition to:

start_date >= :date_a and start_date < (:date_b + 1)

assuming that date_a & date_b are true DATE datatype.

--
Sergio Samayoa
Lgica Software
http://www.geocities.com/logicasw/

Craig,

Thank you for your response. It still doesn't want to go through however and
I have it pretty much like you suggested:

SELECT * FROM SOME_TABLE WHERE SOME_TIME BETWEEN :2/16/2001 3:36:00 PM AND
:2/18/2001 3:36:00 PM

The SOME_TIME field is a TIMESTAMP and just for grins I tried the statement
with quotes and apos around the dates.

Any suggestions?

-Randall

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> wrote in message
news:3B324E97.9F876568@no_spam.vertexsoftware.com...

        Sorry if I wasn't clear.  :StartDate and :StopDate in my example are
*parameters.*  If you want to use constants you need to quote them
instead of using colons.  Also, constant values must be passed to IB in
24 hour time.  Rewriting your query:

SELECT
  *
FROM
  SOME_TABLE
WHERE
  SOME_TIME BETWEEN '2/16/2001 15:36:00' AND '2/18/2001 15:36:00';

        HTH,

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Craig,

I could have swore that I tried what you suggested before and it didn't work
but it did work this time.  <feeling foolish DOH!>

Thanks for your help,

Randall

"Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> wrote in message
news:3B339DFA.35DC4B59@no_spam.vertexsoftware.com...