title: DateTime Conversion Error In Store Procedure Inside Cursor
body: <p>I'm facing error like below when I tried to convert datetime string (yyyy-MM-dd HH:mm TT) to SQL DateTime in Store Procedure inside Cursor.</p>
  
  <blockquote>
    <p>Conversion failed when converting date and/or time from character string.</p>
  </blockquote>
  
  <p>I'm pasting my store procedure below and error throwing at line 131 in <code>INSERT</code> statement where converting 2 variables <code>@var1</code> &amp; <code>@var2</code> to DATETIME.</p>
  
  <p>SQL Server Version:</p>
  
  <blockquote>
    <p>Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.2  (Build 9200: )</p>
  </blockquote>
  
  <p>Variable Values For Testing:</p>
  
  <pre><code>@Log = "8/6/2015 1:37:00 PM"
  @stBreakTimeStart = "1:00 PM"
  @stBreakTimeEnd = "2:00 PM"
  </code></pre>
  
  <p>Approximate <code>@var1</code> &amp; <code>@var2</code> Values For Testing:</p>
  
  <pre><code>@var1 = "2015-08-06 1:00 PM"
  @var2 = "2015-08-06 2:00 PM"
  </code></pre>
  
  <p>My Store Procedure:</p>
  
  <pre><code>-- =============================================
  -- Author:      KG
  -- Create date: 15.09.13
  -- Description: Insert all excel file data in EmpInOutAuto table
  -- EXEC [insertEmpInOutAuto] '&lt;EmpData&gt;&lt;AttendanceData&gt;&lt;stEmployeeName&gt;Dexterity&lt;/stEmployeeName&gt;&lt;inStaffNo&gt;25&lt;/inStaffNo&gt;&lt;stDepartment&gt;Management&lt;/stDepartment&gt;&lt;inDevNo&gt;1&lt;/inDevNo&gt;&lt;dtLog&gt;8/6/2015 1:37:00 PM&lt;/dtLog&gt;&lt;stState&gt;&lt;/stState&gt;&lt;stMode&gt;In&lt;/stMode&gt;&lt;/AttendanceData&gt;&lt;/EmpData&gt;',1
  -- =============================================
  ALTER PROCEDURE [dbo].[insertEmpInOutAuto]
  (
  @ImportXML XML,
  @inTrackExcelSheetId INT
  )
  AS
  BEGIN
  
  DECLARE @intErrorCode INT;
  
  BEGIN TRAN
      INSERT INTO EmpInOutAuto
          (
              [stEmployeeName],
              [inStaffNo],
              [stDepartment],
              [inDevNo],
              [dtLog],
              [stState],
              [stMode],
              [inTrackExcelSheetId]
          )
      SELECT
          Col.value('stEmployeeName[1]', 'NVARCHAR(200)') AS NAME,
          Col.value('inStaffNo[1]', 'INT') AS StaffNo,
          Col.value('stDepartment[1]', 'NVARCHAR(50)') AS Dept,
          Col.value('inDevNo[1]', 'INT') AS DevNo,
          Col.value('dtLog[1]', 'DATETIME') AS DateTimeLog,
          Col.value('stState[1]', 'NVARCHAR(200)') AS StateC,
          Col.value('stMode[1]', 'NVARCHAR(50)') AS Mode,
          @inTrackExcelSheetId
      FROM
          @ImportXML.nodes('//EmpData/AttendanceData') Tab(Col)
  
  
      -- Get BreakTime Data
      DECLARE @stBreakTimeStart NVARCHAR(50);
      DECLARE @stBreakTimeEnd NVARCHAR(50);
      DECLARE @stFinalBTStart NVARCHAR(20);
      DECLARE @stFinalBTEnd NVARCHAR(20);
  
      SELECT @stBreakTimeStart=stStartTime, @stBreakTimeEnd=stEndTime
      FROM BreakTime;
      -- End
  
      DECLARE @EmployeeName NVARCHAR(200);
      DECLARE @StaffNo INT;
      DECLARE @Dept NVARCHAR(50);
      DECLARE @DevNo INT;
      DECLARE @Log DATETIME;
      DECLARE @State NVARCHAR(200);
      DECLARE @Mode NVARCHAR(50);
  
      DECLARE MyCursor CURSOR FOR
          SELECT [stEmployeeName],
                  [inStaffNo],
                  [stDepartment],
                  [inDevNo],
                  [dtLog],
                  [stState],
                  [stMode]
          FROM
                  [EmpInOutAuto]
          WHERE
                  ISNULL([stMode],'Auto')&lt;&gt;'Auto' AND
                  [inTrackExcelSheetId]=@inTrackExcelSheetId
          ORDER BY stEmployeeName ASC, dtLog ASC
  
          OPEN MyCursor
          FETCH NEXT FROM MyCursor INTO @EmployeeName, @StaffNo, @Dept, @DevNo, @Log, @State, @Mode
  
          WHILE @@FETCH_STATUS = 0  
              BEGIN 
  
                  DECLARE @inEmployeeId INT;
                  DECLARE @dtBTStart DATETIME;
                  DECLARE @dtBTEnd DATETIME;
  
                  SET @inEmployeeId = (SELECT inEmployeeId FROM Employee WHERE stEmployeeName=@EmployeeName);
  
                  IF @inEmployeeId IS NULL
                  BEGIN
  
                      INSERT INTO Employee
                          (
                              [inEmpRoleId],
                              [stEmployeeCode],
                              [stUserName],
                              [stPassword],
                              [stEmployeeName],
                              [inStaffNo],
                              [stDepartment],
                              [inDevNo],
                              [stState],
                              [inTrackExcelSheetId]
                          )
                      VALUES
                          (
                              (CASE @Dept
                                  WHEN 'Management' THEN 2
                                  ELSE 3
                              END),
                              @StaffNo,
                              @EmployeeName,
                              (SELECT dbo.getRandomPassword()),
                              @EmployeeName,
                              @StaffNo,
                              @Dept,
                              @DevNo,
                              @State,
                              @inTrackExcelSheetId
                          )
  
                      SET @inEmployeeId = Scope_Identity();
                  END
  
                  DECLARE @var1 NVARCHAR(50);
                  SET @var1 = CONVERT(NVARCHAR(10),CONVERT(DATE, @Log)) + ' ' + @stBreakTimeStart;
                  print(@var1);
  
                  DECLARE @var2 NVARCHAR(50);
                  SET @var2 = CONVERT(NVARCHAR(10),CONVERT(DATE, @Log)) + ' ' + @stBreakTimeEnd;
                  print(@var2);
  
                  INSERT INTO EmpInOut
                      (
                          [inEmployeeId],
                          [dtInOut],
                          [stMode],
                          [dtBreakTimeStart],
                          [dtBreakTimeEnd],
                          [inNumber],
                          [inTrackExcelSheetId]
                      )
                  VALUES
                      (
                          @inEmployeeId,
                          @Log,
                          @Mode,
                          CONVERT(DATETIME,@var1),
                          CONVERT(DATETIME,@var2),
                          (SELECT ISNULL(MAX(inNumber),0)+1 FROM EmpInOut WHERE inEmployeeId=@inEmployeeId),
                          @inTrackExcelSheetId
                      )
  
              FETCH NEXT FROM MyCursor INTO @EmployeeName, @StaffNo, @Dept, @DevNo, @Log, @State, @Mode
          END  
  
          CLOSE MyCursor  
          DEALLOCATE MyCursor 
  
          SELECT 'Successfull!'
  
      SELECT @intErrorCode = @@ERROR
      IF (@intErrorCode &lt;&gt; 0) GOTO PROBLEM
  COMMIT TRAN
  
  PROBLEM:
  IF (@intErrorCode &lt;&gt; 0) BEGIN
      SELECT @intErrorCode + 'Unexpected error occurred!'
      ROLLBACK TRAN
  END
  
  END
  </code></pre>