none
Conversion failed when converting datetime from character string.

    Question

  • I keep getting this error and i've tried several ways to convert the datetime. I need the date set in a variable because ill be passing through different values. Any help is appreciated. 

    DECLARE @RecDateStart DATETIME
    DECLARE @RecDateEnd DATETIME
    DECLARE @ActCapMonthStart DATETIME
    DECLARE @ActCapMonthEnd DATETIME
    DECLARE @MstrID INT
    
    SET @RecDateStart = 2013/01/01
    SET @RecDateEnd = 2013/09/01
    SET @MstrID = 39
    
    DECLARE @strSQL VARCHAR (max)
    DECLARE @DatabaseNameParameter VARCHAR (100)
    
    SET @DatabaseNameParameter = (SELECT dwdbname
                                  FROM   codesets.dbo.tblmstr
                                  WHERE  @MstrID = mstrid)
    SET @strSQL = '	SELECT
    					LobCodeDesc,
    					CR.MemNameF,
    					CR.MemNameL,
    					CR.MemNameM,
    					CR.MemNum AS MemNum,
    					CRD.CapTotal,
    					CR.ActCapMonth,
    					CR.RecEff,
    					MST.Name,
    					CR.NetSpecID,
    					TC.CountyName,
    					MR.PCPNum,
    					MR.PCPName
    				FROM ' + @DatabaseNameParameter
                  + '.dbo.tblCapRevenue CR			WITH(NOLOCK)
    				LEFT JOIN ' + @DatabaseNameParameter
                  + '.dbo.tblCapRevenueDtl CRD		WITH(NOLOCK)	ON CR.ActCapMonth = CRD.ActCapMonth AND CR.MemNum = CRD.MemNum
    				LEFT JOIN CodeSets.dbo.tblLobRelMstr LRM							WITH(NOLOCK)	ON CRD.LobCode = LRM.LobCode
    				LEFT JOIN CodeSets.dbo.tblMstr MST									WITH(NOLOCK)	ON CR.MstrID = MST.MstrID
    				LEFT JOIN CodeSets.dbo.tblcnty TC									WITH(NOLOCK)	ON TC.CountyNo = CR.CntyIdMem
    				LEFT JOIN ' + @DatabaseNameParameter
                  + '.dbo.tblMemRecap MR			WITH(NOLOCK)	ON CR.MemNum = MR.MemNum
    				GROUP BY
    						LobCodeDesc,
    						CR.MemNameF,
    						CR.MemNameL,
    						CR.MemNameM,
    						CR.MemNum,
    						CRD.CapTotal,
    						CR.ActCapMonth,
    						CR.RecEff,
    						MST.Name,
    						CR.NetSpecID,
    						TC.CountyName,
    						MR.PCPNum,
    						MR.PCPName
    				WHERE 
    					RecEff BETWEEN CAST(' + @RecDateStart + ' AS DATETIME) AND '
                  + 'CAST(' + @RecDateEnd + ' AS DATETIME)'
    
    PRINT @strSQL
    
    EXECUTE(@strSQL)

    Monday, September 30, 2013 2:37 PM

Answers

  • You cannot append a DATETIME to a string.  @RecDateStart and @RecDateEnd are already DATETIME data types.

    Use:

    				RecEff BETWEEN CONVERT(DATETIME,''' + CONVERT(varchar(25),@RecDateStart,120) + ''',120) AND '
                  + 'CONVERT(DATETIME,''' + CONVERT(varchar(25),@RecDateEnd,120) + ''',120)'
    
    

    • Marked as answer by TonyG86 Monday, September 30, 2013 3:31 PM
    Monday, September 30, 2013 2:46 PM
    Moderator
  • Hi Tony,

    I got your issue now. I would strongly recommend you to use "sp_executesql" to run your dynamic query, as i have demonstrated in the example below ; 

    DECLARE @RecDateStart DATETIME
    DECLARE @RecDateEnd DATETIME
    
    DECLARE @strSQL NVARCHAR (max)
    DECLARE @ParmDefinition nvarchar(max)
    
    SET @strSQL = N'
    SELECT * FROM [dbo].[demo_claim]
    where claim_date between @IRecDateStart AND @IRecDateEnd'
    
    SET @ParmDefinition = N'@IRecDateStart DATETIME, @IRecDateEnd DATETIME'
    
    SET @RecDateStart = '2013/01/01'
    SET @RecDateEnd = '2013/09/01'
    
    EXECUTE sp_executesql @strSQL, @ParmDefinition,
                          @IRecDateStart = @RecDateStart,
    					  @IRecDateEnd = @RecDateEnd;

    This worked perfect in my test. Here more details on the usage of sp_executesql - http://technet.microsoft.com/en-us/library/ms188001.aspx


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    • Marked as answer by TonyG86 Monday, September 30, 2013 3:42 PM
    Monday, September 30, 2013 3:34 PM

All replies

  • Hi TonyG86,

    Instead of using ' + @RecDateStart + ' in the @strSQL variable, try this:

    '...' + convert(char(8), @RecDateStart, 112) + '...'

    Monday, September 30, 2013 2:45 PM
  • You cannot append a DATETIME to a string.  @RecDateStart and @RecDateEnd are already DATETIME data types.

    Use:

    				RecEff BETWEEN CONVERT(DATETIME,''' + CONVERT(varchar(25),@RecDateStart,120) + ''',120) AND '
                  + 'CONVERT(DATETIME,''' + CONVERT(varchar(25),@RecDateEnd,120) + ''',120)'
    
    

    • Marked as answer by TonyG86 Monday, September 30, 2013 3:31 PM
    Monday, September 30, 2013 2:46 PM
    Moderator
  • Changed it to:

    WHERE 
    					RecEff BETWEEN ' + convert(char(8), @RecDateStart, 112) + ' AND ' + convert(char(8), @RecDateEnd, 112)

    And Resulted in: 

    				WHERE 
    					RecEff BETWEEN 20130101 AND 20130901
    Msg 156, Level 15, State 1, Line 35
    Incorrect syntax near the keyword 'WHERE'.
    

    Monday, September 30, 2013 2:55 PM
  • Try this:

    DECLARE @RecDateStart DATETIME
    DECLARE @RecDateEnd DATETIME
    DECLARE @ActCapMonthStart DATETIME
    DECLARE @ActCapMonthEnd DATETIME
    DECLARE @MstrID INT
    
    SET @RecDateStart = 2013/01/01
    SET @RecDateEnd = 2013/09/01
    SET @MstrID = 39
    
    DECLARE @strSQL VARCHAR (max)
    DECLARE @DatabaseNameParameter VARCHAR (100)
    
    SET @DatabaseNameParameter = (SELECT dwdbname
                                  FROM   codesets.dbo.tblmstr
                                  WHERE  @MstrID = mstrid)
    SET @strSQL = '	SELECT
    					LobCodeDesc,
    					CR.MemNameF,
    					CR.MemNameL,
    					CR.MemNameM,
    					CR.MemNum AS MemNum,
    					CRD.CapTotal,
    					CR.ActCapMonth,
    					CR.RecEff,
    					MST.Name,
    					CR.NetSpecID,
    					TC.CountyName,
    					MR.PCPNum,
    					MR.PCPName
    				FROM ' + @DatabaseNameParameter
                  + '.dbo.tblCapRevenue CR			WITH(NOLOCK)
    				LEFT JOIN ' + @DatabaseNameParameter
                  + '.dbo.tblCapRevenueDtl CRD		WITH(NOLOCK)	ON CR.ActCapMonth = CRD.ActCapMonth AND CR.MemNum = CRD.MemNum
    				LEFT JOIN CodeSets.dbo.tblLobRelMstr LRM							WITH(NOLOCK)	ON CRD.LobCode = LRM.LobCode
    				LEFT JOIN CodeSets.dbo.tblMstr MST									WITH(NOLOCK)	ON CR.MstrID = MST.MstrID
    				LEFT JOIN CodeSets.dbo.tblcnty TC									WITH(NOLOCK)	ON TC.CountyNo = CR.CntyIdMem
    				LEFT JOIN ' + @DatabaseNameParameter
                  + '.dbo.tblMemRecap MR			WITH(NOLOCK)	ON CR.MemNum = MR.MemNum
    				GROUP BY
    						LobCodeDesc,
    						CR.MemNameF,
    						CR.MemNameL,
    						CR.MemNameM,
    						CR.MemNum,
    						CRD.CapTotal,
    						CR.ActCapMonth,
    						CR.RecEff,
    						MST.Name,
    						CR.NetSpecID,
    						TC.CountyName,
    						MR.PCPNum,
    						MR.PCPName
    				WHERE 
    					RecEff BETWEEN ''' + convert(char(8), @RecDateStart, 112) + ' AND '
                  + '''' + convert(char(8), @RecDateEnd, 112) + ''''
    
    PRINT @strSQL
    
    EXECUTE(@strSQL)

    Monday, September 30, 2013 2:58 PM
  • Try

    DECLARE @RecDateStart Char(8) DECLARE @RecDateEnd Char(8) SET @RecDateStart = '20130901' SET @RecDateEnd = '20130901'

    WHERE RecEff BETWEEN @RecDateStart AND @RecDateEnd



    Systems Analyst

    Monday, September 30, 2013 2:59 PM
  • in your table, is the column RecEff, of the data type DATETIME ? 

    You would want to put single quotes around date string in your SET clause . Also, since both your start and end date variables are of DATETIME data type, you don't need to explicitly CAST them into DATETIME datatype in the WHERE clause. Something like this will work for you, if you column on the table on which your date filter is applied, is of DATETIME data type.

    DECLARE @RecDateStart DATETIME
    DECLARE @RecDateEnd DATETIME
    
    SET @RecDateStart = '2013/01/01'
    SET @RecDateEnd = '2013/09/01'
    
    
    SELECT * FROM [dbo].[demo_claim]
    where claim_date between @RecDateStart AND @RecDateEnd


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Monday, September 30, 2013 3:03 PM
  • Same outcome.
    Monday, September 30, 2013 3:04 PM
  • Thats what i started out with which results in: 

    Msg 241, Level 16, State 1, Line 17
    Conversion failed when converting datetime from character string.

    But the dates do have to be wrapped in quotes. Printing the the query shows the wrong dates in the where clause without them. 

    Monday, September 30, 2013 3:05 PM
  • TonyG86,

    You're right !

    Instead of 

    SET @RecDateStart = 2013/01/01
    Use:
    SET @RecDateStart = '2013/01/01'


    Monday, September 30, 2013 3:10 PM
  • Sanil, the below code alone works just fine. It seems the issue is due to executing the query through a variable. 
    DECLARE @RecDateStart DATETIME
    DECLARE @RecDateEnd DATETIME
    
    SET @RecDateStart = '01/01/2013'
    SET @RecDateEnd = '09/01/2013'
    
    select receff from dbo.tblcaprevenue
    where receff between @recdatestart and @recdateend
    Not really sure why this would be the case. 
    Monday, September 30, 2013 3:12 PM
  • Guillaume

    I tried it with the quotes and it doesnt give me a conversion error but it does give me a syntax error in the where clause. 

    Monday, September 30, 2013 3:22 PM
  • TonyG86,

    Sorry, I was missing a quote. Here is the complete query:

    DECLARE @RecDateStart DATETIME
    DECLARE @RecDateEnd DATETIME
    DECLARE @ActCapMonthStart DATETIME
    DECLARE @ActCapMonthEnd DATETIME
    DECLARE @MstrID INT
    
    SET @RecDateStart = '2013/01/01'
    SET @RecDateEnd = '2013/09/01'
    SET @MstrID = 39
    
    DECLARE @strSQL VARCHAR (max)
    DECLARE @DatabaseNameParameter VARCHAR (100)
    
    SET @DatabaseNameParameter = (SELECT dwdbname
                                  FROM   codesets.dbo.tblmstr
                                  WHERE  @MstrID = mstrid)
    
    SET @strSQL = '	SELECT
    					LobCodeDesc,
    					CR.MemNameF,
    					CR.MemNameL,
    					CR.MemNameM,
    					CR.MemNum AS MemNum,
    					CRD.CapTotal,
    					CR.ActCapMonth,
    					CR.RecEff,
    					MST.Name,
    					CR.NetSpecID,
    					TC.CountyName,
    					MR.PCPNum,
    					MR.PCPName
    				FROM ' + @DatabaseNameParameter
                  + '.dbo.tblCapRevenue CR			WITH(NOLOCK)
    				LEFT JOIN ' + @DatabaseNameParameter
                  + '.dbo.tblCapRevenueDtl CRD		WITH(NOLOCK)	ON CR.ActCapMonth = CRD.ActCapMonth AND CR.MemNum = CRD.MemNum
    				LEFT JOIN CodeSets.dbo.tblLobRelMstr LRM							WITH(NOLOCK)	ON CRD.LobCode = LRM.LobCode
    				LEFT JOIN CodeSets.dbo.tblMstr MST									WITH(NOLOCK)	ON CR.MstrID = MST.MstrID
    				LEFT JOIN CodeSets.dbo.tblcnty TC									WITH(NOLOCK)	ON TC.CountyNo = CR.CntyIdMem
    				LEFT JOIN ' + @DatabaseNameParameter
                  + '.dbo.tblMemRecap MR			WITH(NOLOCK)	ON CR.MemNum = MR.MemNum
                  WHERE
    					RecEff BETWEEN ''' + convert(char(8), @RecDateStart, 112) + ''' AND '
                  + '''' + convert(char(8), @RecDateEnd, 112) + '''
    				GROUP BY
    						LobCodeDesc,
    						CR.MemNameF,
    						CR.MemNameL,
    						CR.MemNameM,
    						CR.MemNum,
    						CRD.CapTotal,
    						CR.ActCapMonth,
    						CR.RecEff,
    						MST.Name,
    						CR.NetSpecID,
    						TC.CountyName,
    						MR.PCPNum,
    						MR.PCPName'
    
    PRINT @strSQL
    
    EXECUTE(@strSQL)
    Rem: be carefull, the where clause must be set before the group by
    Monday, September 30, 2013 3:28 PM
  • Tom this worked THANK YOU!

    Monday, September 30, 2013 3:31 PM
  • Hi Tony,

    I got your issue now. I would strongly recommend you to use "sp_executesql" to run your dynamic query, as i have demonstrated in the example below ; 

    DECLARE @RecDateStart DATETIME
    DECLARE @RecDateEnd DATETIME
    
    DECLARE @strSQL NVARCHAR (max)
    DECLARE @ParmDefinition nvarchar(max)
    
    SET @strSQL = N'
    SELECT * FROM [dbo].[demo_claim]
    where claim_date between @IRecDateStart AND @IRecDateEnd'
    
    SET @ParmDefinition = N'@IRecDateStart DATETIME, @IRecDateEnd DATETIME'
    
    SET @RecDateStart = '2013/01/01'
    SET @RecDateEnd = '2013/09/01'
    
    EXECUTE sp_executesql @strSQL, @ParmDefinition,
                          @IRecDateStart = @RecDateStart,
    					  @IRecDateEnd = @RecDateEnd;

    This worked perfect in my test. Here more details on the usage of sp_executesql - http://technet.microsoft.com/en-us/library/ms188001.aspx


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    • Marked as answer by TonyG86 Monday, September 30, 2013 3:42 PM
    Monday, September 30, 2013 3:34 PM
  • Sunil thank you very much for this information. 
    Monday, September 30, 2013 3:43 PM