473,320 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

checking record older than 2 years not working?

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).
Jul 19 '05 #1
5 1781
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"
Jul 19 '05 #2
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:OY**************@tk2msftngp13.phx.gbl...
| 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).
Jul 19 '05 #3
Lord Merlin wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:OY**************@tk2msftngp13.phx.gbl...
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"
Jul 19 '05 #4

Greater love has no one than this, that he lay down his life for his friends
(John 15:13).
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uq**************@TK2MSFTNGP10.phx.gbl...
| Lord Merlin wrote:
| > "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
| > news:OY**************@tk2msftngp13.phx.gbl...
| >> 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
Jul 19 '05 #5
> 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.

--
http://www.aspfaq.com/
(Reverse address to reply.)
Jul 19 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: wheel | last post by:
I have been ok with optimistic locking most of the time but I have a situation now where there is more of a chance that users could try to edit a record at the same time. I'd like to use pessimitic...
2
by: DeanL | last post by:
Hi Everyone, Does anyone know a way to "check" if a record number is in existence when someone is adding a new record? Currently working on an awfully designed Access 97 database (that I'm not...
3
by: helpful sql | last post by:
Hi, I am working on an app that will display the list of all scheduled call records in our database to the users. All users of the application will see the same list of call records and will...
66
by: Johan Tibell | last post by:
I've written a piece of code that uses sockets a lot (I know that sockets aren't portable C, this is not a question about sockets per se). Much of my code ended up looking like this: if...
42
by: =?Utf-8?B?UGxheWE=?= | last post by:
I have an if statement that isn't working correctly and I was wondering how I check for a blank string. My Code Example if me.fieldname(arrayIndex) = "" then ----- end if When I do this and...
6
by: maxx429 | last post by:
I am trying to check if notes have been added to a Memo field on a form if certain other fields have changed. Example: If the user ticks a particular check box on the form, I want to force them to...
25
by: tekctrl | last post by:
Anyone: I have a simple MSAccess DB which was created from an old ASCII flatfile. It works fine except for something that just started happening. I'll enter info in a record, save the record,...
2
by: hakkatil | last post by:
Hi to all, I have a page that inserts excel sheet to access database. I am using asp. What I want to do is to check the inserting record if it is in the database. Basicly checking the dublicate...
7
by: john.cole | last post by:
I have searched all the groups I can, and I still haven't been able to come up the solution I need. I have the following problem. In my form named sbfrmSpoolList, I am entering a job, spool and...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.