Error concerned with storedprocedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[rptGGM]
(
    @FromDate datetime,
    @ToDate datetime,
    @PartyName varchar(50),
    @Type int
)

As
DECLARE @WhrStr VarChar(500)

SET @WhrStr = 
    CASE @Type
    WHEN 1 THEN
     ' And E.ATAAir between '+ @FromDate +' And '+@ToDate

    WHEN 2 THEN
     ' And D.ATASea between '+ @FromDate+ ' And '+@ToDate
    END
exec
(
'SELECT 
      A.FileNumber As [File No],
      S.POString As [PO Numbers],
      G.PartyName As [Exporter Name],
      C.CargoDesc As [Cargo Description],
      Dbo.ActualDate(A.ETA) As ETA,
         Dbo.ActualDate(IsNull(E.ATAAir, D.ATASea)) As ATA,
     S.SIString As [Supplier Invoices],
D.VesselName As [Vessel Name],
      D.VoyageNo As [Voyage No],
     dbo.PackCntDetails(A.FileID) As [Pk/Cnt Details]

FROM
    FileMain A

LEFT JOIN SIPOString S ON S.FileID=A.FileID
LEFT JOIN Party G ON G.PartyID = A.ExporterID
INNER JOIN Cargo C ON C.FileID = A.FileID
LEFT JOIN FileSea D ON D.FileID = A.FileID
LEFT JOIN FileAir E ON E.FileID = A.FileID

WHERE   
     G.PartyName='+@PartyName +' '+@WhrStr


)

While executing above procedure following error is coming Msg 241, Level 16, State 1, Procedure rptGGM, Line 12 Conversion failed when converting datetime from character string. Can anybody to help to solve the error.

From stackoverflow

• You have to quote the datetime values

  ...
  WHEN 1 THEN
      ' And E.ATAAir between '''+ CAST(@FromDate AS varchar(30)) +''' And '''+CAST(@ToDate AS varchar(30)) + ''''
  
  WHEN 2 THEN
      ' And D.ATASea between '''+ CAST(@FromDate AS varchar(30))+ ' And '''+CAST(@ToDate AS varchar(30)) + ''''
  END
  ...
  

  You'll also have the error later on near @partyname:

  ...
  WHERE   
       G.PartyName='''+@PartyName +''' '+@WhrStr
  

  Edit: Added CAST!

  : still same error is coming.Can u suggest any other way?Is there any error in syntax in where condition.If yes,how can i remove it?

  Lucero : -1 for encouraging practices allowing SQL injection

  gbn : How does it concern you or me?

  Lucero : It does concern because people are looking for solutions here at Stackoverflow. So you basically spread the word with information leading to even more bad SQL code prone to injection attacks.

• you need to do an explicit cast from datetime to varchar when constructing @WhrStr. The following should work:

  CAST(@FromDate as varchar)
  

  Lucero : -1 for encouraging practices allowing SQL injection

  pmarflee : Lucero - Just how exactly does suggesting casting a datetime value to a string constitute advocating SQL injection?

• This would be a lot simpler as a straightforward query without the EXEC. Why did you wrap all this in the EXEC?

• Using dynamic SQL is very bad practice, since it not only may fail because of conversion issues and data length, but also allows SQL injection.

  Use a proper query like this:

  SELECT 
           A.FileNumber As [File No],
           S.POString As [PO Numbers],
           G.PartyName As [Exporter Name],
           C.CargoDesc As [Cargo Description],
           Dbo.ActualDate(A.ETA) As ETA,
           Dbo.ActualDate(IsNull(E.ATAAir, D.ATASea)) As ATA,
          S.SIString As [Supplier Invoices],
  D.VesselName As [Vessel Name],
        D.VoyageNo As [Voyage No],
          dbo.PackCntDetails(A.FileID) As [Pk/Cnt Details]
  
  FROM
      FileMain A
  
  LEFT JOIN SIPOString S ON S.FileID=A.FileID
  LEFT JOIN Party G ON G.PartyID = A.ExporterID
  INNER JOIN Cargo C ON C.FileID = A.FileID
  LEFT JOIN FileSea D ON D.FileID = A.FileID
  LEFT JOIN FileAir E ON E.FileID = A.FileID
  
  WHERE   
       G.PartyName=@PartyName AND
       (
             ((@Type = 1) And (E.ATAAir Between @FromDate And @ToDate))
       Or
             ((@Type = 2) And (E.ATASea Between @FromDate And @ToDate))
       )