I dont know why my procedure doesnt work:
SET @fDate = CONVERT(DATE,CA ST(@year AS VARCHAR(25)) +'/'+ @month + '/01',111)
SET @lDate = CONVERT(DATE,CA ST(@year AS VARCHAR(25)) +'/' + @month + '/' + CAST(dbo.DaysIn Month(@fDate) AS VARCHAR(25)),11 1)
INSERT @TempTable --@TempTable is created inside procedure
SELECT Supplier, SUM(Total) AS Quantity, Monthly
FROM
(
SELECT Supplier, Quantity AS Total, DATEPART(mm,CON VERT(DateTime,i .Date,103)) AS Monthly
FROM Imports AS i
WHERE (i.Product like N'%'+@productNa me+'%')
AND (i.Industry like N'%'+@industry+ '%')
AND CONVERT(DateTim e, i.Date, 103) >= @fDate
AND CONVERT(DateTim e, i.Date, 103) <= @lDate
) AS tmp
GROUP BY Supplier, Monthly
If i run it directly from Studio Management, it works fine. However, if i call the procedure from Visual Studio 2010,the procedure returns nothing. The weird thing is that if i replace:
CONVERT(DATE,CA ST(@year AS VARCHAR(25)) +'/'+ @month + '/01',111) by CONVERT(DATE, '2011/' + @month +'/01', 111) then procedure returns correct result. @year is of type INT. When i step debug procedure, and check for @year value, its value is correctly passed. So i come to conclude:
CAST(@year AS VARCHAR(25) != '2011' if @year = 2011. is it weird?
SET @fDate = CONVERT(DATE,CA ST(@year AS VARCHAR(25)) +'/'+ @month + '/01',111)
SET @lDate = CONVERT(DATE,CA ST(@year AS VARCHAR(25)) +'/' + @month + '/' + CAST(dbo.DaysIn Month(@fDate) AS VARCHAR(25)),11 1)
INSERT @TempTable --@TempTable is created inside procedure
SELECT Supplier, SUM(Total) AS Quantity, Monthly
FROM
(
SELECT Supplier, Quantity AS Total, DATEPART(mm,CON VERT(DateTime,i .Date,103)) AS Monthly
FROM Imports AS i
WHERE (i.Product like N'%'+@productNa me+'%')
AND (i.Industry like N'%'+@industry+ '%')
AND CONVERT(DateTim e, i.Date, 103) >= @fDate
AND CONVERT(DateTim e, i.Date, 103) <= @lDate
) AS tmp
GROUP BY Supplier, Monthly
If i run it directly from Studio Management, it works fine. However, if i call the procedure from Visual Studio 2010,the procedure returns nothing. The weird thing is that if i replace:
CONVERT(DATE,CA ST(@year AS VARCHAR(25)) +'/'+ @month + '/01',111) by CONVERT(DATE, '2011/' + @month +'/01', 111) then procedure returns correct result. @year is of type INT. When i step debug procedure, and check for @year value, its value is correctly passed. So i come to conclude:
CAST(@year AS VARCHAR(25) != '2011' if @year = 2011. is it weird?