checking record older than 2 years not working?

Discussion in 'ASP General' started by Lord Merlin, Jul 19, 2004.

  1. Lord Merlin

    Lord Merlin Guest

    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).
     
    Lord Merlin, Jul 19, 2004
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Jul 20, 2004
    #2
    1. Advertising

  3. Lord Merlin

    Lord Merlin Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    | 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).
     
    Lord Merlin, Jul 20, 2004
    #3
  4. Lord Merlin wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> 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"
     
    Bob Barrows [MVP], Jul 20, 2004
    #4
  5. Lord Merlin

    Lord Merlin Guest

    Greater love has no one than this, that he lay down his life for his friends
    (John 15:13).
    "Bob Barrows [MVP]" <> wrote in message
    news:...
    | Lord Merlin wrote:
    | > "Bob Barrows [MVP]" <> wrote in message
    | > news:...
    | >> 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
     
    Lord Merlin, Jul 20, 2004
    #5
  6. > 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.)
     
    Aaron [SQL Server MVP], Jul 20, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Prime
    Replies:
    6
    Views:
    1,937
  2. Shiperton Henethe
    Replies:
    1
    Views:
    334
    brucie
    Oct 17, 2003
  3. ai@work
    Replies:
    9
    Views:
    562
    Ron Natalie
    Dec 16, 2004
  4. Stefan Ram
    Replies:
    1
    Views:
    403
    Arne Vajhøj
    Feb 14, 2009
  5. laredotornado

    checking for at least 18 years old

    laredotornado, Apr 4, 2008, in forum: Javascript
    Replies:
    6
    Views:
    181
    Dr J R Stockton
    Apr 5, 2008
Loading...

Share This Page