CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008
user1804925
user1804925

Reputation: 159

conversion failed when converting date /time from character string

enter image description here

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

Upvotes: 0

Views: 581

Answers (2)

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

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

Upvotes: 1

Hiten004
Hiten004

Reputation: 2491

please use the belowstatement in where clause. I did not test below scripts. 

SELECT [PROC_ID]
        ,[CONTACT_DATE_new]
        ,[CPT_CODE_new]
        ,[CHARGEABLE_YN_new]
        ,cat.CDM_Category
        ,DateUpdated
    FROM
        [dbo].[CDM_Audit_EAP_OT]
where
   (@locationType = 'DateUpdated' AND (DateUpdated BETWEEN @Start_Date AND @End_Date)

CASE 

SELECT [PROC_ID]
        ,[CONTACT_DATE_new]
        ,[CPT_CODE_new]
        ,[CHARGEABLE_YN_new]
        ,cat.CDM_Category
        ,DateUpdated
    FROM
        [dbo].[CDM_Audit_EAP_OT]
    where    
    CASE 
    when @locationType =  'DateUpdated' and (DateUpdated BETWEEN @Start_Date AND @End_Date) then 1
    Else 0
    End = 1

Upvotes: 1

Related Questions