checking record older than 2 years not working?

[Lord Merlin]

Good day to you all

I'm trying to see all the records in the database older than 2 years from
today, and I can't seem to be getting it to go.
SELECT * FROM comments WHERE (currentdate < 2002 - 07 - 20) ORDER BY
currentdate

I get null records returned, even though I can see some records as old as
1995.
When I run the command like this however, it returns all the rows.
SELECT * FROM comments WHERE (currentdate > 2002 - 07 - 20) ORDER BY
currentdate

any suggestions?
--


Kind Regards
Rudi Ahlers
+27 (82) 926 1689

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).

 

[Bob Barrows [MVP]]

Good day to you all

I'm trying to see all the records in the database older than 2 years
from today, and I can't seem to be getting it to go.
SELECT * FROM comments WHERE (currentdate < 2002 - 07 - 20) ORDER
BY currentdate

I get null records returned, even though I can see some records as
old as 1995.
When I run the command like this however, it returns all the rows.
SELECT * FROM comments WHERE (currentdate > 2002 - 07 - 20) ORDER
BY currentdate

any suggestions?

Yes, tell us the type and version of database you are using, and tell us the
datatype of currentdate.

Bob Barrows

PS. .components is not relevant. Why did you crosspost to that group? .db is
the only really relevant group in your list.

 

[Lord Merlin]

| Lord Merlin wrote:
| > Good day to you all
| >
| > I'm trying to see all the records in the database older than 2 years
| > from today, and I can't seem to be getting it to go.
| > SELECT * FROM comments WHERE (currentdate < 2002 - 07 - 20) ORDER
| > BY currentdate
| >
| > I get null records returned, even though I can see some records as
| > old as 1995.
| > When I run the command like this however, it returns all the rows.
| > SELECT * FROM comments WHERE (currentdate > 2002 - 07 - 20) ORDER
| > BY currentdate
| >
| > any suggestions?
|
| Yes, tell us the type and version of database you are using, and tell us
the
| datatype of currentdate.
|
| Bob Barrows
|
| PS. .components is not relevant. Why did you crosspost to that group? .db
is
| the only really relevant group in your list.
| --
| Microsoft MVP - ASP/ASP.NET
| Please reply to the newsgroup. This email account is my spam trap so I
| don't check it very often. If you must reply off-line, then remove the
| "NO SPAM"
|
|
The server is MS SQL 2000, and the data type is datetime. Does this make a
big difference if the databases I use are generally SQL, i.e SQL 7 / SQL
2000 / MySQL / Access?

--


Kind Regards
Rudi Ahlers
+27 (82) 926 1689

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).

 

[Bob Barrows [MVP]]

The server is MS SQL 2000, and the data type is datetime. Does this
make a big difference if the databases I use are generally SQL, i.e
SQL 7 / SQL 2000 / MySQL / Access?

Of course it does! Access uses JetSQL (and VBA functions). SQL Server uses
T-SQL. I have no idea what MySQL uses.

The answer to any query question will ALWAYS depend on the type (and
sometimes the version) of database you are using. Do not keep it a secret.

Anyways, for SQL2000 (and earlier):

SELECT <list of columns> FROM comments
WHERE currentdate <= DATEADD(yy,-2,GETDATE())
ORDER BY currentdate

Bob Barrows

 

[Lord Merlin]

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).
| Lord Merlin wrote:
| > | >> Lord Merlin wrote:
| >>> Good day to you all
| >>>
| >>> I'm trying to see all the records in the database older than 2 years
| >>> from today, and I can't seem to be getting it to go.
| >>> SELECT * FROM comments WHERE (currentdate < 2002 - 07 - 20)
| >>> ORDER BY currentdate
| >>>
| >>> I get null records returned, even though I can see some records as
| >>> old as 1995.
| >>> When I run the command like this however, it returns all the rows.
| >>> SELECT * FROM comments WHERE (currentdate > 2002 - 07 - 20)
| >>> ORDER BY currentdate
| >>>
| >>> any suggestions?
| >>
| >> Yes, tell us the type and version of database you are using, and
| >> tell us the datatype of currentdate.
| >>
| > The server is MS SQL 2000, and the data type is datetime. Does this
| > make a big difference if the databases I use are generally SQL, i.e
| > SQL 7 / SQL 2000 / MySQL / Access?
|
| Of course it does! Access uses JetSQL (and VBA functions). SQL Server uses
| T-SQL. I have no idea what MySQL uses.
|
| The answer to any query question will ALWAYS depend on the type (and
| sometimes the version) of database you are using. Do not keep it a secret.
|
| Anyways, for SQL2000 (and earlier):
|
| SELECT <list of columns> FROM comments
| WHERE currentdate <= DATEADD(yy,-2,GETDATE())
| ORDER BY currentdate
|
| Bob Barrows
| --
| Microsoft MVP - ASP/ASP.NET
| Please reply to the newsgroup. This email account is my spam trap so I
| don't check it very often. If you must reply off-line, then remove the
| "NO SPAM"
|
|

Thanx



--


Kind Regards
Rudi Ahlers
+27 (82) 926 1689

 

[Aaron [SQL Server MVP]]

SELECT * FROM comments WHERE (currentdate < 2002 - 07 - 20) ORDER BY

currentdate

Since you have no delimiters around your oddly-formatted "date", this
becomes an expression, and your query is equivalent to:

SELECT * FROM comments WHERE (currentdate < 1975) ORDER BY currentdate
(Note, that is still a numeric expression, not a date or a year.)

Which is equivalent to:

SELECT * FROM comments WHERE (currentdate < '19050530') ORDER BY currentdate

In addition to the string delimiter problem, you shouldn't be hard-coding
the date into the query at all. Assuming you always want rows more than two
years old, here is how your query *should* be formatted:

SELECT <column_list>
FROM comments
WHERE (currentdate < DATEADD(YEAR, -2, CONVERT(CHAR(8), GETDATE(),
112)))
ORDER BY currentdate


Also, don't use SELECT *:
http://www.aspfaq.com/2096

Follow-ups set to asp.db only. This isn't a components issue, and belongs
only in the db-related group. Just because the group has "asp" in the name
doesn't make your question relevant there.