Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2009
    Posts
    12

    Unanswered: SQL Statement Help Please

    Hi All...
    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?

    -------code--------
    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 '%'

    -------error--------
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid parameter 1 specified for datediff.


    Thanks
    Andy

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2009
    Posts
    12
    Thanks pootle flump
    I had googled it before posting.
    The problem was with the 'd' - i removed the ' :-)

    Thanks
    Andy

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    YYYYMMDDTHH:MI:SS:MMM
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2009
    Posts
    12
    Noted Pootle - and thanks again
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •