SQL date query. Please help me!

Discussion in 'ASP General' started by Si, Jan 15, 2004.

  1. Si

    Si Guest

    Hi there.

    I have a page on a website I am building where is want to look for all
    records added in the last 60 days.

    The date is added in short UK format, that is 25/12/2003 in an access
    database. (set as a date field)

    I am using <% session.lcid=2057 %> in my header to force UK date format.

    My problem is, everything seems to be correct, but the query just returns
    all records in the table.

    The SQL query is

    if request("lookup") = "latest" then
    recent = FormatDateTime(Now()-60,vbShortDate)
    end if
    if request.querystring("lookup") = "latest" then
    myquery="SELECT * FROM Property WHERE datein > " & recent & " ORDER BY
    datein DESC"
    else
    myquery="SELECT * FROM Property WHERE department LIKE '%" &
    request.querystring("lookup") & "%' AND pricefaieuro > " & MinPrice & " AND
    pricefaieuro < " & MaxPrice & " ORDER BY reference DESC"
    end if

    The SQL output via response.write seems to be correct (see following)

    SELECT * FROM Property WHERE datein > 07/10/2003 ORDER BY datein DESC

    (I have also tried WHERE NOT datein < " & recent &" ........)

    Does anyone know what the proplem may be. I have been looking at this for
    hours now and last night and am no closer to fixing it.

    Thanks in advance

    Simon
    Si, Jan 15, 2004
    #1
    1. Advertising

  2. Si

    CJM Guest

    Have you run this SQL in Access itself? Did you get the same results?
    CJM, Jan 15, 2004
    #2
    1. Advertising

  3. Si

    Bob Barrows Guest

    Si wrote:
    > Hi there.
    >
    > I have a page on a website I am building where is want to look for all
    > records added in the last 60 days.
    >
    > The date is added in short UK format, that is 25/12/2003 in an access
    > database. (set as a date field)
    >
    > I am using <% session.lcid=2057 %> in my header to force UK date
    > format.
    >

    Start here:
    http://www.aspfaq.com/show.asp?id=2040

    Where the article says to use "YYYYMMDD" format, it's talking about SQL
    Server. This format will not be recognized by Jet. For Access, it is best to
    use "YYYY-MM-DD". And don't forget to delimit the date literal with hash
    marks (#).

    HTH,
    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, Jan 15, 2004
    #3
  4. Si

    Roland Hall Guest

    "Si" wrote:
    : if request.querystring("lookup") = "latest" then
    : myquery="SELECT * FROM Property WHERE datein > " & recent & " ORDER BY
    : datein DESC"

    if request.querystring("lookup") = "latest" then
    myquery="SELECT * FROM Property WHERE datein > '" & recent & "' ORDER BY
    datein DESC"

    Instead of returning this:

    SELECT * FROM Property WHERE datein > 07/10/2003 ORDER BY datein DESC

    ....it should return this:

    SELECT * FROM Property WHERE datein > '07/10/2003' ORDER BY datein DESC

    You need the quotes around the date.

    HTH...

    --
    Roland

    This information is distributed in the hope that it will be useful, but
    without any warranty; without even the implied warranty of merchantability
    or fitness for a particular purpose.
    -Technet Knowledge Base-
    http://support.microsoft.com/default.aspx?scid=fh;EN-US;kbhowto&sd=TECH&ln=EN-US&FR=0
    -Technet Script Center-
    http://www.microsoft.com/technet/treeview/default.asp?url=/technet/scriptcenter/default.asp
    -MSDN Library-
    http://msdn.microsoft.com/library/default.asp
    Roland Hall, Jan 15, 2004
    #4
  5. Si

    TomB Guest

    If it was me I'd create a query in Access that returns the data from the
    last 60 days, and call that query rather than dynamically building a SQL
    Statement.

    "Si" <simon@MY_PANTSnetforma.com> wrote in message
    news:O8uNb.1843$...
    > Hi there.
    >
    > I have a page on a website I am building where is want to look for all
    > records added in the last 60 days.
    >
    > The date is added in short UK format, that is 25/12/2003 in an access
    > database. (set as a date field)
    >
    > I am using <% session.lcid=2057 %> in my header to force UK date format.
    >
    > My problem is, everything seems to be correct, but the query just returns
    > all records in the table.
    >
    > The SQL query is
    >
    > if request("lookup") = "latest" then
    > recent = FormatDateTime(Now()-60,vbShortDate)
    > end if
    > if request.querystring("lookup") = "latest" then
    > myquery="SELECT * FROM Property WHERE datein > " & recent & " ORDER BY
    > datein DESC"
    > else
    > myquery="SELECT * FROM Property WHERE department LIKE '%" &
    > request.querystring("lookup") & "%' AND pricefaieuro > " & MinPrice & "

    AND
    > pricefaieuro < " & MaxPrice & " ORDER BY reference DESC"
    > end if
    >
    > The SQL output via response.write seems to be correct (see following)
    >
    > SELECT * FROM Property WHERE datein > 07/10/2003 ORDER BY datein DESC
    >
    > (I have also tried WHERE NOT datein < " & recent &" ........)
    >
    > Does anyone know what the proplem may be. I have been looking at this for
    > hours now and last night and am no closer to fixing it.
    >
    > Thanks in advance
    >
    > Simon
    >
    >
    >
    >
    >
    >
    TomB, Jan 15, 2004
    #5
    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. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,244
    Michael Borgwardt
    May 30, 2004
  2. Anonymous
    Replies:
    0
    Views:
    1,455
    Anonymous
    Oct 13, 2005
  3. KK
    Replies:
    2
    Views:
    546
    Big Brian
    Oct 14, 2003
  4. IluDeR
    Replies:
    0
    Views:
    246
    IluDeR
    Jan 21, 2004
  5. IluDeR
    Replies:
    0
    Views:
    237
    IluDeR
    Jan 21, 2004
Loading...

Share This Page