Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > checking record older than 2 years not working?

Reply
Thread Tools

checking record older than 2 years not working?

 
 
Lord Merlin
Guest
Posts: n/a
 
      07-19-2004
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).


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-19-2004
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"


 
Reply With Quote
 
 
 
 
Lord Merlin
Guest
Posts: n/a
 
      07-19-2004
"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
| 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).


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      07-20-2004
Lord Merlin wrote:
> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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"


 
Reply With Quote
 
Lord Merlin
Guest
Posts: n/a
 
      07-20-2004

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).
"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
| Lord Merlin wrote:
| > "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
| > news:(E-Mail Removed)...
| >> 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


 
Reply With Quote
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      07-20-2004
> 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(, 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.

--
http://www.aspfaq.com/
(Reverse address to reply.)


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Java on historic (i.e., older than 10 years) Windows versions (was: Displaying a Text upside Down using an applet) Stefan Ram Java 1 02-14-2009 01:37 AM
checking for at least 18 years old laredotornado Javascript 6 04-05-2008 07:12 PM
Program to list all dirs, subdirs and files older than 1 year Prime Java 6 12-15-2005 08:56 AM
" Invalid Disk Table in Boot Record - Boot Record could not be repaired " reply@newsgroup.please Computer Support 2 12-01-2003 05:37 AM
How can I stop users from viewing pages older than (e.g.) 10 minutes... ? Shiperton Henethe HTML 1 10-17-2003 10:32 PM



Advertisments