Board index » delphi » Timestamp formats

Timestamp formats

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

 

Re:Timestamp formats


Quote
Randall Fidler wrote:

> 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;

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

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

Quote
> I have found thatworking with timestamps in Interbase is not easy

        Really?  What problem are you having?

Quote
> and the
> extract function doesn't look to friendly either.  

        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

Re:Timestamp formats


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/

Re:Timestamp formats


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...

Quote

> Randall Fidler wrote:

> > 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;

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

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

> > I have found thatworking with timestamps in Interbase is not easy

> Really?  What problem are you having?

> > and the
> > extract function doesn't look to friendly either.

> 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

Re:Timestamp formats


Quote
Randall Fidler wrote:

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

        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

Re:Timestamp formats


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...

Quote

> Randall Fidler wrote:

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

> 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

Other Threads