M. wrote on Sat, 24 Nov 2007 14:36:30 +0200:
thank you for your response. Firstly MSSQL is too expensive. MSSQL 2005
express seemed even slower than access in queries. (I had recently upsized
from access) amd it lacks the tools for tuning and I am not a dataabse
expert so I am not sure how to tune the 2005 XPRESS to perform
better.
MSSQL 2005 is free, so I can only assume you're "too expensive" reference is
directed at the other editions. The tuning tools mostly involve creating
indexes. Upsizing from Access isn't great, it leaves a lot to be desired
(although I haven't upsized since Access 97, so maybe the tools got better,
but I doubt it). If you can't work out what indexes you should be creating
in MS SQL, do you really think using MySQL will help?
here is one query that doesnt work:
SELECT COUNT(AD_ID) AS unpaid FROM tblADS WHERE A_PAID = 0 AND A_USER =
1 AND A_STATUS > 0 AND DateDiff(h, now(), A_DATE_END) > 0
its the datediff function that I cannot find a way to make it work in
mysql
Searching on Google for MySQL Datediff throws up lots of results, most of
which will make it clear that the syntax for datediff is different.
eg.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_datediff
MySQL's datediff only takes 2 arguments, and returns the number of days.
You can't use it to work out the number of hours difference, you would have
to use DATE_ADD instead to add hours to the current time and compare to the
column in the database.
Dan