Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Stored Procedure not running - conversion issue

    Hi folks,

    Hope someone can help me with a stored procedure I've created.

    The stored procedure gets a set of dates using a cursor and builds a string variable.
    I then am trying to use the string variable I built to filter on another select query.

    When I try and run the procedure I get the error:

    Msg 295, Level 16, State 3, Procedure usp_app_monthly_figures, Line 43
    Conversion failed when converting character string to smalldatetime data type.
    Code:
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    
        DECLARE @extract_dates nvarchar(512), @month nvarchar(64), @extract_date datetime
        SET @extract_dates = ''
    
        -- Insert statements for procedure here
        DECLARE app_cursor CURSOR FOR
            SELECT DISTINCT TOP (100) PERCENT { fn MONTHNAME(EntryDate) } AS Month, MAX(EntryDate) AS ExtractDate
            FROM         dbo.Applications
            WHERE     (sqlAcYear = '2010')
            GROUP BY { fn MONTHNAME(EntryDate) }
            ORDER BY ExtractDate
    
        OPEN app_cursor
        FETCH NEXT FROM app_cursor INTO @month, @extract_date
        WHILE @@FETCH_STATUS = 0
        BEGIN
        
            SET @extract_dates = @extract_dates + 'CONVERT(DATETIME, ''' + CONVERT(varchar(10), CAST(DATEPART(DAY, @extract_date) AS varchar) + '/' + CAST(DATEPART(MONTH, @extract_date) AS varchar) + '/' + CAST(DATEPART(YEAR, @extract_date) as varchar)) + '''), '
    
            FETCH NEXT FROM app_cursor INTO @month, @extract_date
        END
    
        CLOSE app_cursor
    
        DEALLOCATE app_cursor
    END
    
        SET @extract_dates = SUBSTRING(@extract_dates, 1, LEN(@extract_dates)-1)
    
    BEGIN
        SELECT DISTINCT TOP (100) PERCENT { fn MONTHNAME(EntryDate) } AS Month, EntryDate, 
            SUM(DISTINCT sqlp + sqlack + sqlao + sqlrw + sqlrd + sqlapa + sqliq + sqldna + sqlintv + sqlna + sqlowl + sqlwdrw + sqliap + sqlconfa + sqlconfb) AS Applications
        FROM dbo.Applications
        WHERE (sqlAcYear = '2010') AND (EntryDate IN (@extract_dates))
        GROUP BY EntryDate, { fn MONTHNAME(EntryDate) }
        ORDER BY EntryDate
    END
    <- Hides behind a rock.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I think you're making it more complex than it should be. Remove your cursor completely, dump the results of the SELECT used as a cursor definition into a temp table, and then replace "...AND (EntryDate IN (@extract_dates))" with "...AND EXISTS (sleect * from #temp_table_where_you_stored_ExtractDate_and_Month _fields t where Applications.ExtractDate = t.ExtractDate)"
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Thanks.
    That's got me back on track now.
    <- Hides behind a rock.

Posting Permissions

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