another date query

[Alistair]

this is really bugging me as I can't see why this is happening.

I have a page which runs as the reult of a search, records details of the
search, it goes a bit like this.

get dearch criteria and search datebase.

if no data is found then create a record for that search, including the
search query, the search count (set to 1), and the search date.

strSQL = "INSERT INTO SEARCH_STATS (query_details, search_number,
last_search_date) VALUES (" & "'" & ucase(query_details) & "'" & ",1," &
date() & ")"

if the search data is found then increase the search count by one and amend
the last search date.

old_search_count = rs1("search_number")
new_search_count = old_search_count + 1
rs1("search_number") = new_search_count
rs1("last_search_date") = date()
rs1.update
now, if no search data is found then when the search record is created the
date that gets inserted is all wrong (it looks more like a time format)

if the search data is found the date that gets updated becomes correct.

what I can't figure out is how two dates() on the same page can give
different results???

[Aaron [SQL Server MVP]]

BTW, your table is going to get quite large, assuming a decent amount of
activity. I used to maintain information about all searches at aspfaq.com,
now I've resorted to only tracking searches that get 0 results. (That helps
me narrow down the list of future articles.)

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

[Aaron [SQL Server MVP]]

> old_search_count = rs1("search_number")

new_search_count = old_search_count + 1
rs1("search_number") = new_search_count
rs1("last_search_date") = date()
rs1.update
Ugh! How about:

conn.execute "UPDATE Search_Stats SET search_number = search_number + 1,
last_search_date = GETDATE() WHERE Search_Details = '" & query_details &
"'", , 129
strSQL = "INSERT INTO SEARCH_STATS
(query_details, search_number, last_search_date)
VALUES (" & "'" & ucase(query_details) & "'"
& ",1," & date() & ")"

You forgot to put delimiters around your date, so the database sees
06/01/2004 as a number (6 divided by 1 divided by 2004). But more to the
point, why do you think you need to pass a date from ASP? The database is
certainly capable of determining the date, without opening your application
up to weird formatting issues like the above, or misinterpreted regional
settings (dd/mm/yyyy vs. mm/dd/yyyy, etc).

strSQL = "INSERT ... ,1, GETDATE())"

(If you are using Access, you will need to change GETDATE() to Date(). Once
again, it is always useful to tell us a little bit about the software you're
using, so we don't have to guess and/or make assumptions.)

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