Loading ...
Sorry, an error occurred while loading the content.

SQL Server - 'Date' is not a recognized built-in function name.

Expand Messages
  • MBDEV - DB
    I am not sure how to format the last part of this query which comes from Access.: SELECT Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.TitleID,
    Message 1 of 3 , Mar 21, 2013
    • 0 Attachment
      I am not sure how to format the last part of this query which comes from
      Access.:

      SELECT Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.TitleID,
      TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID

      FROM (TITLES_WARNING_SENT_qry RIGHT JOIN TITLES ON
      TITLES_WARNING_SENT_qry.TitleID = TITLES.TitleID) INNER JOIN TITLOC ON
      TITLES.TitleID = TITLOC.TitleID

      WHERE TITLES_WARNING_SENT_qry.TitleID Is Null

      GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID,
      TITLES.TitleID

      HAVING (TITLES.RecDT<Date()-31)


      I am getting:
      'Date' is not a recognized built-in function name.

      It is probably something simple but how would I go about converting this
      part "HAVING (TITLES.RecDT<Date()-31)" to something SQL Server is happy
      with?

      Thanks,

      Mike



      [Non-text portions of this message have been removed]
    • Mark E
      Change date() to getdate() Also, look up the datediff function as that may be a better way to do it. Mark ________________________________ From: MBDEV - DB
      Message 2 of 3 , Mar 21, 2013
      • 0 Attachment
        Change date() to getdate()

        Also, look up the datediff function as that may be a better way to do it.

        Mark




        ________________________________
        From: MBDEV - DB <databaselists@...>
        To: SQLQueriesNoCode@yahoogroups.com
        Sent: Thursday, March 21, 2013 8:09 PM
        Subject: [SQLQueriesNoCode] SQL Server - 'Date' is not a recognized built-in function name.


         
        I am not sure how to format the last part of this query which comes from
        Access.:

        SELECT Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.TitleID,
        TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID

        FROM (TITLES_WARNING_SENT_qry RIGHT JOIN TITLES ON
        TITLES_WARNING_SENT_qry.TitleID = TITLES.TitleID) INNER JOIN TITLOC ON
        TITLES.TitleID = TITLOC.TitleID

        WHERE TITLES_WARNING_SENT_qry.TitleID Is Null

        GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID,
        TITLES.TitleID

        HAVING (TITLES.RecDT<Date()-31)

        I am getting:
        'Date' is not a recognized built-in function name.

        It is probably something simple but how would I go about converting this
        part "HAVING (TITLES.RecDT<Date()-31)" to something SQL Server is happy
        with?

        Thanks,

        Mike

        [Non-text portions of this message have been removed]




        [Non-text portions of this message have been removed]
      • MBDEV - DB
        Hi, I ended up with this: SELECT MAX(dbo.TITLOC.TitleLocID) AS MaxOfTitleLocID, dbo.TITLES.TitleID, dbo.TITLES.CustLName, dbo.TITLES.CustFName,
        Message 3 of 3 , Mar 22, 2013
        • 0 Attachment
          Hi,





          I ended up with this:
          SELECT MAX(dbo.TITLOC.TitleLocID) AS MaxOfTitleLocID, dbo.TITLES.TitleID, dbo.TITLES.CustLName, dbo.TITLES.CustFName, dbo.TITLES.RecDT,

          dbo.TITLES.TitleID AS Expr1

          FROM dbo.TITLES_WARNING_SENT_qry RIGHT OUTER JOIN

          dbo.TITLES ON dbo.TITLES_WARNING_SENT_qry.TitleID = dbo.TITLES.TitleID INNER JOIN

          dbo.TITLOC ON dbo.TITLES.TitleID = dbo.TITLOC.TitleID

          WHERE (dbo.TITLES_WARNING_SENT_qry.TitleID IS NULL) AND (dbo.TITLES.RecDT < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) - 31, 0))

          GROUP BY dbo.TITLES.CustLName, dbo.TITLES.CustFName, dbo.TITLES.RecDT, dbo.TITLES.TitleID, dbo.TITLES.TitleID



          Thanks






          From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Mark E
          Sent: Thursday, March 21, 2013 8:47 PM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: Re: [SQLQueriesNoCode] SQL Server - 'Date' is not a recognized built-in function name.





          Change date() to getdate()

          Also, look up the datediff function as that may be a better way to do it.

          Mark

          ________________________________
          From: MBDEV - DB <databaselists@... <mailto:databaselists%40outlook.com> >
          To: SQLQueriesNoCode@yahoogroups.com <mailto:SQLQueriesNoCode%40yahoogroups.com>
          Sent: Thursday, March 21, 2013 8:09 PM
          Subject: [SQLQueriesNoCode] SQL Server - 'Date' is not a recognized built-in function name.



          I am not sure how to format the last part of this query which comes from
          Access.:

          SELECT Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.TitleID,
          TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID

          FROM (TITLES_WARNING_SENT_qry RIGHT JOIN TITLES ON
          TITLES_WARNING_SENT_qry.TitleID = TITLES.TitleID) INNER JOIN TITLOC ON
          TITLES.TitleID = TITLOC.TitleID

          WHERE TITLES_WARNING_SENT_qry.TitleID Is Null

          GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID,
          TITLES.TitleID

          HAVING (TITLES.RecDT<Date()-31)

          I am getting:
          'Date' is not a recognized built-in function name.

          It is probably something simple but how would I go about converting this
          part "HAVING (TITLES.RecDT<Date()-31)" to something SQL Server is happy
          with?

          Thanks,

          Mike

          [Non-text portions of this message have been removed]

          [Non-text portions of this message have been removed]





          [Non-text portions of this message have been removed]
        Your message has been successfully submitted and would be delivered to recipients shortly.