Professional Web Applications Themes

compare a date in db field to today - ASP Database

Hi I have a date field in Access that I want to return if the date is less than 30 days old. i have built this query, but I'm not using the DateDiff function correctly Query1 = "Select productID, productName, dateCreate" Query1 = Query1 & " FROM tblProducts" Query1 = Query1 & " WHERE dateCreate" & (DateDiff("d",dateCreate,Date())) < 30 I get the error: Variable is undefined: 'dateCreate' Which I understand, but how do I fix it ? Darren...

  1. #1

    Default compare a date in db field to today

    Hi

    I have a date field in Access that I want to return if the date is less than
    30 days old.

    i have built this query, but I'm not using the DateDiff function correctly

    Query1 = "Select productID, productName, dateCreate"
    Query1 = Query1 & " FROM tblProducts"
    Query1 = Query1 & " WHERE dateCreate" & (DateDiff("d",dateCreate,Date())) <
    30

    I get the error: Variable is undefined: 'dateCreate'

    Which I understand, but how do I fix it ?

    Darren


    Darren Heinrich Guest

  2. #2

    Default Re: compare a date in db field to today

    > Query1 = "Select productID, productName, dateCreate"
    > Query1 = Query1 & " FROM tblProducts"
    > Query1 = Query1 & " WHERE dateCreate" & (DateDiff("d",dateCreate,Date()))
    <
    > 30
    You've mixed up the column name (which is inside the string) with some
    variable outside the string... in fact your comparison (< 30) is also
    outside the SQL statement.

    How about:

    sql = "SELECT ProductID, productName, dateCreate " & _
    " from tblProducts " & _
    " WHERE dateCreate >= DateAdd(""d"", -30, Date())"

    --
    Aaron Bertrand
    SQL Server MVP
    [url]http://www.aspfaq.com/[/url]


    Aaron Bertrand [MVP] Guest

  3. #3

    Default Re: compare a date in db field to today

    Thanks Aaron,

    before i heard from you, I fixed it with

    Query4 = Query4 & " WHERE (DateDiff(""d"",dateCreate,Date())) < 90"

    Works a charm - thanks anyway

    Darren

    "Aaron Bertrand [MVP]" <aaronTRASHaspfaq.com> wrote in message
    news:eS$jodG7DHA.2416TK2MSFTNGP10.phx.gbl...
    > > Query1 = "Select productID, productName, dateCreate"
    > > Query1 = Query1 & " FROM tblProducts"
    > > Query1 = Query1 & " WHERE dateCreate" &
    (DateDiff("d",dateCreate,Date()))
    > <
    > > 30
    >
    > You've mixed up the column name (which is inside the string) with some
    > variable outside the string... in fact your comparison (< 30) is also
    > outside the SQL statement.
    >
    > How about:
    >
    > sql = "SELECT ProductID, productName, dateCreate " & _
    > " from tblProducts " & _
    > " WHERE dateCreate >= DateAdd(""d"", -30, Date())"
    >
    > --
    > Aaron Bertrand
    > SQL Server MVP
    > [url]http://www.aspfaq.com/[/url]
    >
    >

    Darren Heinrich Guest

  4. #4

    Default Re: compare a date in db field to today

    Darren Heinrich wrote:
    > Thanks Aaron,
    >
    > before i heard from you, I fixed it with
    >
    > Query4 = Query4 & " WHERE (DateDiff(""d"",dateCreate,Date())) < 90"
    >
    > Works a charm - thanks anyway
    >
    Maybe it works like a charm now when there aren't many records, but if you
    database grows, this "charm" may turn into a lead weight :-)

    Avoid using functions on columns in your WHERE clause. It causes a table
    scan, i.e., the column value has to be tested in every row of your table.
    This can slow things down, even if you put an index on the column. Always
    strive to put the function on your comparison value instead. This will allow
    the query engine to use an index if you have one on the field, greatly
    speeding it up. My suggestion would be to do it the way Aaron suggested.

    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 Guest

  5. #5

    Default Re: compare a date in db field to today

    Isn't using DateAdd putting a function in the where clause ?


    "Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message
    news:uCDy05K7DHA.804tk2msftngp13.phx.gbl...
    > Darren Heinrich wrote:
    > > Thanks Aaron,
    > >
    > > before i heard from you, I fixed it with
    > >
    > > Query4 = Query4 & " WHERE (DateDiff(""d"",dateCreate,Date())) < 90"
    > >
    > > Works a charm - thanks anyway
    > >
    > Maybe it works like a charm now when there aren't many records, but if you
    > database grows, this "charm" may turn into a lead weight :-)
    >
    > Avoid using functions on columns in your WHERE clause. It causes a table
    > scan, i.e., the column value has to be tested in every row of your table.
    > This can slow things down, even if you put an index on the column. Always
    > strive to put the function on your comparison value instead. This will
    allow
    > the query engine to use an index if you have one on the field, greatly
    > speeding it up. My suggestion would be to do it the way Aaron suggested.
    >
    > 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"
    >
    >

    Darren Heinrich Guest

  6. #6

    Default Re: compare a date in db field to today

    Big difference between

    WHERE function(column) [compare] constant

    vs

    WHERE column [compare] function(constant)

    Even better to put this in a stored query, as another of Bob's points
    mentioned.

    --
    Aaron Bertrand
    SQL Server MVP
    [url]http://www.aspfaq.com/[/url]




    "Darren Heinrich" <dazzjazzozemail.com.au> wrote in message
    news:I%UUb.349$J9.10867nnrp1.ozemail.com.au...
    > Isn't using DateAdd putting a function in the where clause ?
    >
    >
    > "Bob Barrows" <reb01501NOyahoo.SPAMcom> wrote in message
    > news:uCDy05K7DHA.804tk2msftngp13.phx.gbl...
    > > Darren Heinrich wrote:
    > > > Thanks Aaron,
    > > >
    > > > before i heard from you, I fixed it with
    > > >
    > > > Query4 = Query4 & " WHERE (DateDiff(""d"",dateCreate,Date())) < 90"
    > > >
    > > > Works a charm - thanks anyway
    > > >
    > > Maybe it works like a charm now when there aren't many records, but if
    you
    > > database grows, this "charm" may turn into a lead weight :-)
    > >
    > > Avoid using functions on columns in your WHERE clause. It causes a table
    > > scan, i.e., the column value has to be tested in every row of your
    table.
    > > This can slow things down, even if you put an index on the column.
    Always
    > > strive to put the function on your comparison value instead. This will
    > allow
    > > the query engine to use an index if you have one on the field, greatly
    > > speeding it up. My suggestion would be to do it the way Aaron suggested.
    > >
    > > 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"
    > >
    > >
    >
    >

    Aaron Bertrand [MVP] Guest

  7. #7

    Default Re: compare a date in db field to today

    Darren Heinrich wrote:
    > Isn't using DateAdd putting a function in the where clause ?
    Yes, but I advised against " ... using functions _on columns_ in your WHERE
    clause", not simply using functions. In your query, the DateDiff function is
    using the column as its argument. In the alternative, the function is using
    a value as its argument.

    The difference is: in your query, since the column is used in its argument,
    that function has to be run against every row in your table. In other words,
    it has to go to the first row, calculate the result of the formula, and see
    if the result is less than 90. Then again with the second row, and so on.

    In the alternative, it only needs to be run once. It figures out, once, what
    the date 90 days ago was, and then it compares that value to the values in
    your column. If you have an index on that column, the results will be
    obtained very quickly.

    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 Guest

  8. #8

    Default Re: compare a date in db field to today

    Darren Heinrich wrote:
    > Isn't using DateAdd putting a function in the where clause ?
    >
    Here is a good article about optimizing query performance. It has a good
    explanation about sargable and non-sargable search conditions:
    [url]http://www.sql-server-performance.com/sql_server_performance_audit8.asp[/url]

    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 Guest

  9. #9

    Default Re: compare a date in db field to today


    Thanks very much Bob for your help

    Darren


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Darren Heinrich Guest

Similar Threads

  1. Help with Today's Date Display
    By kdennis4 in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: April 9th, 08:33 PM
  2. date compare
    By TurboMini in forum Coldfusion - Getting Started
    Replies: 8
    Last Post: June 29th, 06:48 AM
  3. Filtering by today's date
    By benkayuk in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 5th, 01:38 PM
  4. compare date on cd to system date
    By Rowan Ferguson in forum Macromedia Director Lingo
    Replies: 2
    Last Post: October 2nd, 04:41 AM
  5. Compare Date problem
    By Simon in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 11th, 10:19 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