counting days in a query

Thread view:

Ike - 26 Dec 2003 19:47 GMT

How can I make a query such that in the "WHERE" part of my clause, I want to
put something to the effect that, say, X days have elapsed since a given
date.
For example, suppose in my table, I have a boolean field and a date field.
I want to create a query asking for those rows where X days have elapsed
from the date field in a row and the boolean, say, is false.

Do I need to create a third field, called, say "X" for how many days have
elapsed between the date field and today, and update every row in the table
every day?

Thanks, Ike

Reply to this Message

Bjorn Abelli - 26 Dec 2003 21:00 GMT

"Ike" wrote...

> How can I make a query such that in the "WHERE" part
> of my clause, I want to put something to the effect
> that, say, X days have elapsed since a given date.

This is not a Java-related question, and you should try to ask such
questions in a database-specific group.

> For example, suppose in my table, I have a boolean
> field and a date field. I want to create a query
> asking for those rows where X days have elapsed
> from the date field in a row and the boolean, say, is false.

Most databases has built-in-functions to compare dates, but in most cases
they are database-specific. When you ask such questions, please also make a
note on which database you're using. If the database conforms to SQL99, I
don't even think that you need special date-functions.

> Do I need to create a third field, called, say "X"
> for how many days have elapsed between the date field
> and today, and update every row in the table
> every day?

No, this should in most cases not be necessary, but you'll have to look up
in the documentation for the specific database how the Date- and
Boolean-types are implemented, and which functions you need for the query.

Just an example, not db-specific and probably wrong for most databases:

SELECT *
FROM myTable
WHERE (CURRENT_DATE - myDateField) >= 10
AND myBooleanField = TRUE;

// Bjorn A

Reply to this Message

Lee Fesperman - 27 Dec 2003 21:59 GMT

> "Ike" wrote...
> > For example, suppose in my table, I have a boolean
[quoted text clipped - 15 lines]
> in the documentation for the specific database how the Date- and
> Boolean-types are implemented, and which functions you need for the query.

Good answers.

> Just an example, not db-specific and probably wrong for most databases:
>
> SELECT *
> FROM myTable
> WHERE (CURRENT_DATE - myDateField) >= 10
> AND myBooleanField = TRUE;

For standard SQL, the difference between two dates yields an interval datatype. The
specific type of interval must declared with a modifier:

(CURRENT_DATE - myDateField) DAY

yields a DAY INTERVAL datatype ... the number of days between the two dates.

Signature

Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Reply to this Message

counting days in a query

Free Magazines