Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Unanswered: '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

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by belcherman View Post
    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?
    I am guessing that Date() returns todays date from the system.

    Try:

    Code:
    HAVING Titles.RecDT < DateAdd(dd, -31, GetDate())

  3. #3
    Join Date
    Apr 2012
    Posts
    213
    I believe it is better to put this condition in clause Where.
    And if it is necessary to remove also the portion of the time try:

    Code:
    WHERE TITLES_WARNING_SENT_qry.TitleID Is Null AND
          Titles.RecDT < DateAdd(dd, DateDiff(dd, 0, GetDate()) - 31, 0)
    Hope this helps.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    DATE is a data type, not a function

    T-SQL uses the ANSI/ISO DATE, TIME and CURRENT_TIMESTAMP syntax now. But you will need to use the proprietary DATEADD() function. Google up an article on temporal stuff in T-SQL.

  5. #5
    Join Date
    Sep 2007
    Posts
    18
    Thanks guys this stuff worked

Tags for this Thread

Posting Permissions

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