I'm in the process of migrating my Access DB to MS SQL. Now i'm at the point of testing my Classic ASP Application code and have come accross a problem pointing to 'datediff'.
Here is my Statement and the error. Can anyone advise please?
SELECT a.*, b.FirstName+' '+b.LastName AS FullName, c.OrderStatus
FROM (Orders AS a INNER JOIN Customers AS b ON a.CustomerID = b.CustomerID) INNER JOIN OrderStatuses AS c ON a.OrderStatusID = c.OrderStatusID
WHERE OrderID like '%' and DateDiff('d','1/1/2000',OrderDate)>=0 and DateDiff('d','1/1/2030',OrderDate) <=0 and a.OrderStatusID like '%'
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid parameter 1 specified for datediff.
You will come across millions of these sort of things if you don't make some sort of effort to correct them.
You need to look at the syntax for T-SQL DateDiff - it is not quite the same DateDiff sql server - Google Search
It's also worth noting (and this is not well documented) that using that form of date format is subject to errors (it is not language and locale independent). ISO 8061 is one of the better standards to follow. You can find it under the entry for "CAST and CONVERT" in Books Online