CODEWITHSUNDEEP
sql-server-2014
Perumal
Perumal

Reputation: 69

How to solve "Error converting data type varchar to date "

I have a stored procedure like this. If I try to execute it for 1st of june to 31st of july, it shows a conversion error. Can anyone please help how to fix this? It shows the results for 1st of june and 30th of july working fine. But if I use 31st july it's not working.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Samp]
      (@StartDate date ,
       @EndDate date,
       @Flowid int)
AS
BEGIN
    if(@Flowid = 909)
    begin
        select 
            COUNT(distinct a.Audit_id)  
        from  
            [Prod_Workflow_Client41].[dbo].[CustomValues909] a 
        join 
            [Prod_Workflow_Client41].[dbo].[Audit] b on a.id = b.id
        join 
            [Prod_Workflow_Client41].[dbo].[Substatus] c on c.Id = b.Substatus_id
        where 
            c.MajorStatus <> 'Cancelled' 
            and a.Auditor_Reviewed_Date >= @StartDate
            and a.Auditor_Reviewed_Date <= @EndDate
     end
     else if @Flowid = 1010
     begin 
         select 
             COUNT(distinct a.Audit_id)  
         from  
             [Prod_Workflow_Client41].[dbo].[CustomValues1010] a 
         join 
             [Prod_Workflow_Client41].[dbo].[Audit] b on a.id = b.id
         join 
             [Prod_Workflow_Client41].[dbo].[Substatus] c on c.Id = b.Substatus_id
         where 
             c.MajorStatus <> 'Cancelled' 
             and a.Auditor_Reviewed_Date >= @StartDate
             and a.Auditor_Reviewed_Date <= @EndDate
    end
    else if @Flowid = 101
    begin
        select 
            COUNT(distinct a.Audit_id)  
        from  
            [Prod_Workflow_Client41].[dbo].[CustomValues101] a 
        join 
            [Prod_Workflow_Client41].[dbo].[Audit] b on a.id = b.id
        join 
            [Prod_Workflow_Client41].[dbo].[Substatus] c on c.Id = b.Substatus_id
        where 
            c.MajorStatus <> 'Cancelled' 
            and a.Auditor_Reviewed_Date >= @StartDate
            and a.Auditor_Reviewed_Date <= @EndDate
   end
end
--exec [dbo].[Samp] '01/06/2015','31/06/2015',909
--exec [dbo].[Samp] '01/06/2015','30/06/2015',1010
--exec [dbo].[Samp] '01/06/2015','30/06/2015',101

Upvotes: 0

Views: 1042

Answers (1)

steoleary
steoleary

Reputation: 9278

SQL server uses the US date format (mm/dd/yyyy) for these implicit conversions, so entering '01/06/2015' is actually converted to the 6th of January 2015, which is obviously a valid date. If you enter '31/07/2015' SQL server will attempt to convert that to the 7th day of the 31st month, which is invalid and why you get the conversion error.

Also, in your example, you've specified the 31st of June 2015, which isn't a date.

Upvotes: 1

Related Questions