Hi, I have the following situation. in my database i have a date field, [Effective Date] which is in datetime format:'2011-04-14 07:08:39.000' so i created a report which requires only date in the report so used the expression in the store proc as CONVERT(VARCHAR(10),[Effective Date] , 111) as 'Effective Date' and in the where condition i have given [Effective Date] IN (@EffectiveDate).. so generally i need to have multiple date values in the filter, so in the report i created a dataset EFDate_DS which gives distinct date values and set the parameter (@EffectiveDate) available values to EFDate_DS field In the Data set i wrote sql query as SELECT DISTINCT CONVERT(VARCHAR(10),[Effective Date],111) AS [Effective Date] FROM Daily_Attendance UNION SELECT ' ALL' AS [Effective Date] result: ALL 2011/03/31 2011/04/01 2011/04/04 2011/04/05 2011/04/06 2011/04/07 2011/04/22 so when i previewed the report i am getting error error: The property 'ValidValues' of report parameter 'EffectiveDate' doesn't have the expected type so my requirement is to see only date field not the datetime field in the whole report. and other problem , Is it not possible to use the datetime field in spilt function to pass multiple dates?

Please check the data type of the parameter EffectiveDate.

its date time where i have given in the storeprocedure.

Thats what I told you. You changed the datatype of the column to varchar in your stored procedure and you are setting it up as datetime in the parameter properties. Change the type to Text in the parameter properties.

I changed the parameter data type to text and in the dataset i wrote the query as SELECT DISTINCT LEFT(CONVERT(VARCHAR(10), [Effective Date], 111), 10) AS EffectiveDate FROM Daily_Attendance i am able to get the date values as: 2011/03/31 2011/04/01 2011/04/07 2011/05/03 2011/04/15 when i pass these dates .... i am not able to get the data? and also if i check multiple dates i am getting the error :error converting datatype nvarchar to datetime

You'll need to also convert the data type in the WHERE clause in your stored procedure. Why don't you just change the type(from datetime to date) in report designer? If you are using stored procedure, you have to do a couple of more things in order to be able pass multiple parameter values into the stored procedure. Take a look at this post - http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/

Finally i got the date values in the filter but I am not able to pass multiple values in the filter.....I used the same procedure as in the link like adding the joinfunction in the dataset ... .. I am getting this error: Error Converting data type nvarchar to datetime. Is it not possible to pass the datevalues in the filter?

I have used split function in my store proc: ALTER procedure [dbo].[Proc_email] @date varchar(max) as begin declare @setdate varchar(max) set @setdate = convert(varchar(10),@date, 101) SELECT [name] ,[email] ,convert(varchar(10),[date], 101)[date] FROM [TestDB].[dbo].[email] where [date] IN (SELECT * FROM udfMultiValueParm(@setdate,',')) end After using this storeproc i am able to select multiple values but i am not able to get data.

You'll need to also convert the data type in the WHERE clause in your stored procedure. Think about it, the column in your stored procedure is different than the one you used in the report because you changed the data type.

please correct me i f i am wrong where convert(varchar(10),[date], 101) IN (SELECT * FROM udfMultiValueParm(@setdate,',')) or where [date] IN (SELECT * FROM udfMultiValueParm(convert(varchar(10),[date], 101),',')) which one i use the first statement is wrong as we get the error , if we use the second statement i am getting the error in the report.......can you help me out how to do that?

Finally resolved all the issues : ALTER procedure [dbo].[Proc_email] @date varchar(max) as begin SELECT [name] ,[email] , convert(varchar(10),[date], 101) AS date1 from [TestDB].[dbo].[email] where ([date] IN (SELECT * FROM udfMultiValueParm(@date,',')) or ' ALL' IN (@date)) end this should be final storeproc may be i was missing here is to take the date parameter as varchar(max) instead of datetime.

my dataset for effective date is : SELECT distinct convert(varchar(10),[date], 101)[date] FROM [TestDB].[dbo].[email] union select ' ALL' as [date] i have passed the values union with 'ALL', when i select dates and 'ALL' in the filter and run the report i am getting the error:Conversion failed when converting date and/or time from character string? how can i resolve the error?

Hi Aravind T, Looks like you have space before the single quote 'SPACE ALL' try this, SELECT distinct convert(varchar(10),[date], 101)[date] FROM [TestDB].[dbo].[email] union select 'ALL' as [date OR try this, CREATE TABLE #TEMP ( [DATE] DATE ) INSERT INTO #TEMP VALUES ( '2011-05-06' ) INSERT INTO #TEMP VALUES ( '2011-04-06' ) INSERT INTO #TEMP VALUES ( '2011-05-06' ) INSERT INTO #TEMP VALUES ( '2011-03-06' ) INSERT INTO #TEMP VALUES ( '2011-05-06' ) ;WITH CTE AS ( SELECT --[DATE], DISTINCT CONVERT ( VARCHAR, [DATE], 101 ) AS NEW_DATE FROM #TEMP UNION SELECT 'ALL' AS NEW_DATE ) SELECT * FROM CTE DROP TABLE #TEMP Thanks KumarKG, MCTS

Hi Aravind T, Looks like you have space before the single quote 'SPACE ALL' try this, SELECT distinct convert(varchar(10),[date], 101)[date] FROM [TestDB].[dbo].[email] union select 'ALL' as [date OR try this, CREATE TABLE #TEMP ( [DATE] DATE ) INSERT INTO #TEMP VALUES ( '2011-05-06' ) INSERT INTO #TEMP VALUES ( '2011-04-06' ) INSERT INTO #TEMP VALUES ( '2011-05-06' ) INSERT INTO #TEMP VALUES ( '2011-03-06' ) INSERT INTO #TEMP VALUES ( '2011-05-06' ) ;WITH CTE AS ( SELECT --[DATE], DISTINCT CONVERT ( VARCHAR, [DATE], 101 ) AS NEW_DATE FROM #TEMP UNION SELECT 'ALL' AS NEW_DATE ) SELECT * FROM CTE DROP TABLE #TEMP Thanks KumarKG, MCTS