March 27, 2012 at 8:32 am
HI All ,
Below is my script:
USE [DevSalesSecondDrawer]
GO
/****** Object: StoredProcedure [Neg].[UsersCreateStaticsGet] Script Date: 03/27/2012 09:47:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [Neg].[UsersCreateStaticsGet]
(@UserID typeSalesID =null,
@StartDate date ,
@EndDate date
)
As
Begin
if LEN(@UserID) = 0
set @user-id = null
Select OwnerID,COUNT(*) as JobsCreated
into #jobsCount
from Neg.jobs
where (OwnerID = @user-id or @user-id is null ) and
(CAST(CONVERT(CHAR(10),CreatedDate,120) as DateTime)
between CAST (CONVERT (CHAR(10),@StartDate,120)as Date) and CAST (CONVERT(CHAR(10),@EndDate,120)as Date))
group by OwnerID
select AlternateOwnerID as OwnerID, COUNT(*) as AlternatesCreated
into #AlternatesCount
From Neg.Alternates
where (AlternateOwnerID = @user-id or @user-id is null ) and
(CAST(CONVERT(CHAR(10),CreatedDate,120) as DateTime)
between CAST (CONVERT(CHAR(10), @StartDate,120)as Date) and CAST( CONVERT (CHAR(10), @EndDate,120)as Date))
group by AlternateOwnerID
select CreatedByUser as OwnerID , COUNT(*) as ItemsCreated
into #ItemsCount
From neg.Items
where (CreatedByUser = @user-id or @user-id is null ) and
(CAST (CONVERT(CHAR(10),CreatedDate,120) as DateTime )
between CAST(CONVERT (CHAR(10),@Startdate,120) AS Date) and CAST(CONVERT (CHAR(10),@EndDate,120)as Date))
group by CreatedByUser
--Select * from #JobsCount
--Select * from #ItemsCount
--Select * from #alternatesCount
Select ISNUll (ISNULL(j.OwnerID, a.OwnerID), i.OwnerID )as OwnerID ,j.JobsCreated,a.AlternatesCreated,i.ItemsCreated
from #JobsCount J
full outer join #alternatesCount a
on j.OwnerID=a.OwnerID
full outer join #ItemsCount i
on a.OwnerID=i.OwnerID
--drop table #JobsCount
--drop table #Itemscount
--drop table #alternatesCount
End
the above stored procedure is running fine SSMS.But , When I am running from the reports. I am getting the error message.
Implicit conversion from data type sql_variant to date is not allowed. Use the CONVERT function to run this query. (.Net SqlClient Data Provider)
Please help with this.
March 27, 2012 at 8:53 am
Please don't cross post. direct all replies here. http://www.sqlservercentral.com/Forums/Topic1273526-8-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 31, 2012 at 4:06 am
Not sure if that helps but I had a similar problem and I seem to have fixed it by changing my stored procedure parameter type from DATE to SMALLDATETIME.
I assume that this is because DATE type did not exist at the time of SSRS 2005 so it must have used SQL Variant...
The bizarre thing is that it seemed to work for a quite while before it started to show that error...
May 5, 2014 at 3:00 pm
I'm posting because I had this issue and by googling discovered it's a fairly common problem, but no clearcut solutions were described. I puzzled it out from the clues in the error message.
I had the same issue while modifying old C#.net 1.1 framework in Visual Studio .NET 2003. I needed a new textbox control to collect date only input so I created the column in SQL server 2008 as a Date data type then I realized that this framework version didn't recognize the Date (only) data type so I changed it to smalldatetime. Unbeknowst to me at the time, the code initially created the command line to add the parameter using "variant" instead of DateTime.
I would suggest anyone who receives this error should search for the word "variant" in their code, then change it to smalldatetime. For example in the following line of code, change the word "variant" to some other data type:
this.cmdUpdate.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SomeDate", System.Data.SqlDbType.Variant, 4, "SomeDate"));
May 5, 2014 at 3:35 pm
dmswebdev (5/5/2014)
I'm posting because I had this issue and by googling discovered it's a fairly common problem, but no clearcut solutions were described. I puzzled it out from the clues in the error message.I had the same issue while modifying old C#.net 1.1 framework in Visual Studio .NET 2003. I needed a new textbox control to collect date only input so I created the column in SQL server 2008 as a Date data type then I realized that this framework version didn't recognize the Date (only) data type so I changed it to smalldatetime. Unbeknowst to me at the time, the code initially created the command line to add the parameter using "variant" instead of DateTime.
I would suggest anyone who receives this error should search for the word "variant" in their code, then change it to smalldatetime. For example in the following line of code, change the word "variant" to some other data type:
this.cmdUpdate.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SomeDate", System.Data.SqlDbType.Variant, 4, "SomeDate"));
Since the original post was a duplicate post and replies were directed to the other thread, did you check the other thread for an answer?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply