conversion failed when converting date /time from character string

Question

SELECT [PROC_ID]
    ,[CONTACT_DATE_new]
    ,[CPT_CODE_new]
    ,[CHARGEABLE_YN_new]
    ,cat.CDM_Category
    ,DateUpdated
FROM
    [dbo].CDM_Audit_EAP_OT]
WHERE 
    @ColDate BETWEEN @Start_Date AND @End_Date

After I add a new parameter @ColDate, I start getting the above error.

@ColDate has "CONTACT_DATE_new" or "DateUpdated". Users will choose either one depend on what they are looking for.

If I just specify the column directly, the report will work fine.

 WHERE CONTACT_DATE_new BETWEEN @Start_Date AND @End_Date
 WHERE DateUpdated BETWEEN @Start_Date AND @End_Date

Answer 1

The @ColDate in your query is not treated as a column name, instead as a string type variable. When you try to compare its values with @startdate and @enddate it fails. Hence the error. You can create a stored procedure as follows to suit your need :-

Create PROCEDURE [SSRSReport]

@Start_Date date,
@End_Date date,
@ColDate nvarchar(25)

AS

BEGIN
SET NOCOUNT ON

declare @sql1 nvarchar(max)

if @ColDate = 'CONTACT_DATE_new'

begin
set @sql1 = 
'SELECT [PROC_ID]
    ,[CONTACT_DATE_new]
    ,[CPT_CODE_new]
    ,[CHARGEABLE_YN_new]
    ,cat.CDM_Category
    ,DateUpdated
FROM
    [dbo].CDM_Audit_EAP_OT]
WHERE 
    CONTACT_DATE_new BETWEEN ''' + cast(Convert(nvarchar(10),@Start_Date ,120) as Nvarchar(max)) +''' 
    AND ''' + cast(Convert(nvarchar(10),@End_Date ,120) as Nvarchar(max)) + ''''
    end

    else if @ColDate = 'DateUpdated'

    begin
set @sql1 = 
'SELECT [PROC_ID]
    ,[CONTACT_DATE_new]
    ,[CPT_CODE_new]
    ,[CHARGEABLE_YN_new]
    ,cat.CDM_Category
    ,DateUpdated
FROM
    [dbo].CDM_Audit_EAP_OT]
WHERE 
    DateUpdated BETWEEN ''' + cast(Convert(nvarchar(10),@Start_Date ,120) as Nvarchar(max)) +''' 
    AND ''' + cast(Convert(nvarchar(10),@End_Date ,120) as Nvarchar(max)) + ''''
    end

    print @sql1
    exec sp_executesql @sql1

end