Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > SQL date query. Please help me!

Reply
Thread Tools

SQL date query. Please help me!

 
 
Si
Guest
Posts: n/a
 
      01-15-2004
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






 
Reply With Quote
 
 
 
 
CJM
Guest
Posts: n/a
 
      01-15-2004
Have you run this SQL in Access itself? Did you get the same results?


 
Reply With Quote
 
 
 
 
Bob Barrows
Guest
Posts: n/a
 
      01-15-2004
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"


 
Reply With Quote
 
Roland Hall
Guest
Posts: n/a
 
      01-15-2004
"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...&ln=EN-US&FR=0
-Technet Script Center-
http://www.microsoft.com/technet/tre...er/default.asp
-MSDN Library-
http://msdn.microsoft.com/library/default.asp


 
Reply With Quote
 
TomB
Guest
Posts: n/a
 
      01-15-2004
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$(E-Mail Removed)...
> 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
>
>
>
>
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP .Net 1 11-08-2003 09:14 PM
Given a date, how to find the beginning date and ending date of that week Matt C Programming 3 11-08-2003 09:07 PM
Given a date, how to find the beginning date and ending date of that week Matt C++ 2 11-08-2003 08:30 PM



Advertisments