Problems with Dates

[holty]

Hi,

I have another problem with dates, I am creating a site that sells cars, so i have decided to put in a menu with
the number of that model of car in brackets. I have had many problems with this and the only code that works
is too large.

I simply want to count the number of records in the database in the last 14 days where the modelid is equal to
the one passed in.

I have tried:

SELECT Count(tblAdvert.ModelID) FROM tblAdvert WHERE (((DateDiff("d",[tblAdvert].[Date],Now()))<=14) AND ((tblAdvert.ModelID)=2));

This works in the access query environment and returns a value, however when i try it in the web front end i get:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/webpub/index_copy(1).asp, line 257

strSQLBeetle = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE (((DateDiff("d",[tblAdvert].[Date],Now()))<=14) AND ((tblAdvert.VWModelID)=2));"
------------------------------------------------------------------------------------^


So I have changed the "d" to day and it expects more parameters.

Anyone got a better idea of how to count the number of records in the last 14 days?

I don't want to be doing:

recordcount=0
'select records in last 14 days
do while not rsBMW.EOF
If DateDiff("d",rsBMW("Date"),Now()) <= 14 then
recordcount = recordcount+1
end if
rsBMW.movenext
loop

for every model - the page will never load for a 56k!!

Any help would be magic!!

[Roy Sinclair]

You've got double quotes inside a literal, you need to "double-up" those double quotes to get them properly into the string.

strSQLBeetle = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE (((DateDiff(""d"",[tblAdvert].[Date],Now()))<=14) AND ((tblAdvert.VWModelID)=2));"

[holty]

Roy,

Thanks for that, now I get the same response if I used day instead of "d"

I get:

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Any ideas?

[pinkcat_02]

Roy mean to use ""d"" i think this is what doubling the quotes...even u use day it should be ""day""...as far as I understood it is nothing with using d or day but instead it's with quotes...try it if it doesn't work ask Roy

[Morgoth]

Originally posted by holty
I don't want to be doing:

recordcount=0
'select records in last 14 days
do while not rsBMW.EOF
If DateDiff("d",rsBMW("Date"),Now()) <= 14 then
recordcount = recordcount+1
end if
rsBMW.movenext
loop

for every model - the page will never load for a 56k!!

Acually, since your server is doing all the work with the code, and your server is only sending out the result (html code) then it would send the same size of an html document for a 56k as it would with 1000 of those date different loops you have there... It just might take the server longer to proccess... So...

Have you tried a function? If you can't add it to the SQL statement, turn this into a function, and you only print it once...

[Roy Sinclair]

I took a quick look at the SQL references and I think your code should be:


strSQLBeetle = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE (((DateDiff(day,[tblAdvert].[Date],Now()))<=14) AND ((tblAdvert.VWModelID)=2));"


d and dd are both abbreviations for day but even the word day doesn't need to be quoted so the whole problem with quotes is moot. I don't see a function named "NOW()" for getting the current date either so you might want to try GETDATE() instead.

[holty]

Roy,

When i change "d" to day - it expects more parameters. The NOW() returns the current date and time.

Why do I always get problems with dates!!:D

Morgoth,

How would i go about creating a function? I haven't tried this before and it may be much easier :)

[whammy]

Did you try getdate() ?

I haven't been able to use Now() within a SQL statement...

Also instead of using that method, you might just want to try BETWEEN().

http://www.w3schools.com/sql/sql_between.asp

If you're using Access instead of SQL Server, you'd need to use # around your dates instead of single quotes.

[Morgoth]

Originally posted by holty
Morgoth,

How would i go about creating a function? I haven't tried this before and it may be much easier :)

A function is easy and very simple to make.
First off you have to realize that a function RETURNS A VALUE!


Function MyNewFunction(ByVal IntNumber)

Dim IntNewNumber

IntNewNumber = IntNumber + 1
'This is the code that does what you need

MyNewFunction = IntNewNumber
'This makes it so you can do this: Response.Write MyNewFunction(14); Result = 15

End Function



Understand the basics of a function?
If so, you can find more by google.

(sorry, I am very busy at the moment)

[whammy]

Hehe, functions are your best friend. In Classic ASP they are more of a really cool acquaintance that you can count on, though.

I have some pretty useful (since I use them all the time, I can vouch that they work just fine) functions here:

http://www.solidscripts.com/displayscript.asp?sid=10

The IsWhatever() functions work just like any built-in VBScript function, such as IsDate() or IsNumeric() by returning a boolean value (true or false).

The FormatWhatever() functions work the same way - i.e. the same as the built-in FormatCurrency() function in VBScript.

In .NET you could make the same type of function (although if you're using C# you can make it much less bloated, since VB sucks in that regard) a full blown inheritable class... I love this stuff... more on that later!

[holty]

Right.......

So really I could do with a function that I could pass in the model ID and it would return a number (which is a count) of the adverts that wasplaced in the last 14 days..

How would I call this function in my script?

[whammy]

I wouldn't bother with a function for that, I'd just use

strSQLBeetle = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE DateDiff(day,[tblAdvert].[Date],getdate()) <= 14 AND tblAdvert.VWModelID = 2"

I don't see why that wouldn't work.

[holty]

Still having problems with this,

I have inserted 39 records manually into the database with date from 10/02/03 to 20/03/03

When i count the records for a particular model within the last 14 days using:

strSQL = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE DateDiff(day,[tblAdvert].[Date],getdate()) <= 14 AND tblAdvert.VWModelID = 1;"

I get back: MK1(36)

36 records, the only ones that are not included are 01/03/03, 02/03/03 and 03/03/03


So using this query statement isn't producing the correct number of records

Any ideas?

[holty]

sorry ignore last post

this is the prob I get now:

Undefined function 'getdate' in expression.


This is the code:
<%
'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE DateDiff(day,[tblAdvert].[Date],getdate()) <= 14 AND tblAdvert.VWModelID = 2;"
'Open the recordset with the SQL query
rsBeetle.Open strSQL, adoCon
%>



<tr>
<td width="100%" class="middle" bgcolor="#C0C0C0">
<b>&nbsp;
<a href="searchresults.asp?VWModelID=2">» Beetle (<% response.write rsBeetle(0) %>)</a></b></td>
</tr>
<%
'Reset server objects
rsBeetle.Close
Set rsBeetle = Nothing
%>


If i change the getdate() to be NOW()

I get:

Too few parameters. Expected 1.

Any ideas?

[whammy]

iF YOU'RE oops caps lock... anyway, if you're going to use Now(), use it as an ASP variable, like:

"SQL stuff and '" & Now() & "'"

;)

[holty]

whammy, cheers - think I may be getting somewhere now !!

Have a wee look at this:

strSQL = "SELECT Count(tblAdvert.VWModelID) FROM tblAdvert WHERE DateDiff(day,[tblAdvert].[Date], " & NOW() & ") <= 14 AND tblAdvert.VWModelID = 2;"


I'm now getting:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'DateDiff(day,[tblAdvert].[Date], 25/03/2003 6:21:11 PM) <= 14 AND tblAdvert.VWModelID = 2'.

Please be something easy!:D

[whammy]

Yeah you need single quotes around Now() like:

'" & Now() & "'

I believe that's what's causing the problem...

[holty]

whammy just done that and its gone back to the old error 'Too few parameters. Expected 1' - Any ideas?

[whammy]

Hmm... lemme see how I would write this:

strSQL = "SELECT Count(VWModelID) AS MyCount FROM tblAdvert WHERE DateDiff(day,[tblAdvert].[Date], '" & NOW() & "') <= 14 AND VWModelID = 2"

Response.Write(strSQL) : Response.End 'For debugging - comment out otherwise!

Try that out, and use rs("MyCount") as your count variable. I'm thinking that using "Date" as a field name might not be good, but you have that in brackets so that shouldn't be a problem...

[whammy]

Also, is the model id an integer? If it's a text field (probably not though) it would also need single quotes around it. The ending semicolon is not necessary.