Professional Web Applications Themes

checking record older than 2 years not working? - ASP Components

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 ...

  1. #1

    Default 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).


    Lord Merlin Guest

  2. #2

    Default 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).


    Lord Merlin Guest

  3. #3

    Default Re: checking record older than 2 years not working?

    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] Guest

  4. #4

    Default Re: checking record older than 2 years not working?

    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] Guest

  5. #5

    Default Re: checking record older than 2 years not working?

    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:OYwJ6SebEHA.3012tk2msftngp13.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).


    Lord Merlin Guest

  6. #6

    Default Re: checking record older than 2 years not working?

    Lord Merlin wrote:
    > "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    > news:OYwJ6SebEHA.3012tk2msftngp13.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"


    Bob Barrows [MVP] Guest

  7. #7

    Default Re: checking record older than 2 years not working?


    Greater love has no one than this, that he lay down his life for his friends
    (John 15:13).
    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:uql4X2ebEHA.3888TK2MSFTNGP10.phx.gbl...
    | Lord Merlin wrote:
    | > "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    | > news:OYwJ6SebEHA.3012tk2msftngp13.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


    Lord Merlin Guest

  8. #8

    Default Re: checking record older than 2 years not working?

    > 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 *:
    [url]http://www.aspfaq.com/2096[/url]

    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.

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


    Aaron [SQL Server MVP] Guest

  9. #9

    Default Re: checking record older than 2 years not working?

    > 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 *:
    [url]http://www.aspfaq.com/2096[/url]

    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.

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


    Aaron [SQL Server MVP] Guest

  10. #10

    Default Re: checking record older than 2 years not working?

    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    news:uyx#xylbEHA.716TK2MSFTNGP11.phx.gbl...
    | > 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 *:
    | [url]http://www.aspfaq.com/2096[/url]
    |
    | 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.
    |
    | --
    | [url]http://www.aspfaq.com/[/url]
    | (Reverse address to reply.)
    |
    |

    Thanx for the reply, and a lot was learnt from this little exercise :)

    The reason for the SELECT *, is that I actually just want to delete all
    entries, from a few tables, older than 2 years, and I would then simply do


    DELETE FROM comments WHERE (currentdate < DATEADD(YEAR, -2, CONVERT(CHAR(8),
    GETDATE(),112)))

    Is this safe, and viable?

    --


    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 Guest

  11. #11

    Default Re: checking record older than 2 years not working?

    Lord Merlin wrote:
    > The reason for the SELECT *, is that I actually just want to delete
    > all entries, from a few tables, older than 2 years, and I would then
    > simply do
    >
    >
    > DELETE FROM comments WHERE (currentdate < DATEADD(YEAR, -2,
    > CONVERT(CHAR(8), GETDATE(),112)))
    >
    > Is this safe, and viable?
    Yes. This is the correct syntax.

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows [MVP] Guest

  12. #12

    Default Re: checking record older than 2 years not working?

    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:#7sXGpobEHA.1292TK2MSFTNGP10.phx.gbl...
    | Lord Merlin wrote:
    | > The reason for the SELECT *, is that I actually just want to delete
    | > all entries, from a few tables, older than 2 years, and I would then
    | > simply do
    | >
    | >
    | > DELETE FROM comments WHERE (currentdate < DATEADD(YEAR, -2,
    | > CONVERT(CHAR(8), GETDATE(),112)))
    | >
    | > Is this safe, and viable?
    |
    | Yes. This is the correct syntax.
    |
    | Bob Barrows
    |
    | --
    | Microsoft MVP -- ASP/ASP.NET
    | Please reply to the newsgroup. The email account listed in my From
    | header is my spam trap, so I don't check it very often. You will get a
    | quicker response by posting to the newsgroup.
    |
    |

    From this thread, and learning experience, what would be the best format to
    save date & info into the DB then?
    My current format is 2004/01/13 01:00:00 PM
    --


    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 Guest

  13. #13

    Default Re: checking record older than 2 years not working?

    Lord Merlin wrote:
    > From this thread, and learning experience, what would be the best
    > format to save date & info into the DB then?
    Dates are not stored with any format. They are stored as numbers: Double in
    Access, paired Integers in SQL Server. The format used to display the dates
    depends on the machine on which the dates are displayed.
    > My current format is 2004/01/13 01:00:00 PM
    That's fine for Access. The database engine should have no trouble
    interpreting that. In SQL Server 20040113 1:00:00 PM is very safe as well.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows [MVP] Guest

  14. #14

    Default Re: checking record older than 2 years not working?

    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:#FPIdUpbEHA.3512TK2MSFTNGP12.phx.gbl...
    | Lord Merlin wrote:
    |
    | > From this thread, and learning experience, what would be the best
    | > format to save date & info into the DB then?
    |
    | Dates are not stored with any format. They are stored as numbers: Double
    in
    | Access, paired Integers in SQL Server. The format used to display the
    dates
    | depends on the machine on which the dates are displayed.
    |
    | > My current format is 2004/01/13 01:00:00 PM
    |
    | That's fine for Access. The database engine should have no trouble
    | interpreting that. In SQL Server 20040113 1:00:00 PM is very safe as well.
    |
    | Bob Barrows
    | --
    | Microsoft MVP -- ASP/ASP.NET
    | Please reply to the newsgroup. The email account listed in my From
    | header is my spam trap, so I don't check it very often. You will get a
    | quicker response by posting to the newsgroup.
    |
    |
    Ok, I suppose this would be my last request on this topic, but how do I
    check for dates older than 14 days from now?

    delete FROM tblMsgBoard WHERE dtLastPost < DATEADD(day, -14,
    CONVERT(CHAR(8), GETDATE(),112))

    Does this look right?


    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 Guest

  15. #15

    Default Re: checking record older than 2 years not working?

    Lord Merlin wrote:
    > Ok, I suppose this would be my last request on this topic, but how do
    > I check for dates older than 14 days from now?
    >
    > delete FROM tblMsgBoard WHERE dtLastPost < DATEADD(day, -14,
    > CONVERT(CHAR(8), GETDATE(),112))
    >
    > Does this look right?
    >
    >
    Yes.
    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] Guest

Similar Threads

  1. Replies: 21
    Last Post: March 24th, 07:45 PM
  2. Replies: 2
    Last Post: August 9th, 03:50 AM
  3. Older Mac OS versions and older Macs
    By maron in forum Mac Applications & Software
    Replies: 11
    Last Post: July 29th, 02:13 AM
  4. Auto create record in subform NZ is not working
    By Ken Ivins in forum Microsoft Access
    Replies: 3
    Last Post: July 14th, 05:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139