2 Replies - 325 Views - Last Post: 19 December 2013 - 01:51 AM

#1 marvinmartian  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 07-October 09

Syntax error converting datetime to character string or integer

Posted 18 December 2013 - 10:04 PM

im trying to get an output of total minutes late but the problem is this
"Syntax error converting datetime from character string"
here my code
SELECT DISTINCT
			OT.CtrlNo, dbo.fn_EmployeeNo (T.CTRLNo) AS Employee_No,
			dbo.fn_EmplName2 (T.CTRLNo) AS Employee_Name,
			dbo.fn_Days(OT.SchedIdx) AS Days, OT.SchedIdx, CONVERT(CHAR(10),T.RecordDate,101) AS LOG_DATE,
			CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, T.RecordDate), 108) AS [Biometrics LogIn],
			CASE WHEN CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 2, T.RecordDate),108) IS NULL THEN
			CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 1, T.RecordDate),108) ELSE 
			CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 2, T.RecordDate),108) END [Biometrics LogOut],
			--LogIn AM
			CASE WHEN CONVERT(CHAR(8),OT.OfficialTimeIn,108) >= 
			CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialTimeIn,108) 
				ELSE 
				case when CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, T.RecordDate),108)>CONVERT(CHAR(8),OT.OfficialLunchOut,108) then
				cast(0 as char(4)) else
				CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, T.RecordDate),108) END end [TimeIn_AM],
			--End Here
			--TimeOutAM
			CASE WHEN CONVERT(CHAR(8),OT.OfficialLunchOut,108) <= CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialLunchOut,108) ELSE CONVERT(CHAR(8),dbo.FN_Get1stLogout (T.CtrlNo, T.RecordDate, T.RecordDate), 108) END TimeOut_AM,
			--End Here
			--Computation of Minutes AM
			DATEDIFF(mi,CASE WHEN CONVERT(CHAR(8),OT.OfficialTimeIn,108) >= CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialTimeIn,108) 
			ELSE
			case when CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, T.RecordDate),108)>CONVERT(CHAR(8),OT.OfficialLunchOut,108) then cast(0 as char(4))--null 

				ELSE CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, T.RecordDate),108) END END,
				CASE WHEN CONVERT(CHAR(8),OT.OfficialLunchOut,108) <= CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 1, T.RecordDate),108) THEN
				CONVERT(CHAR(8),OT.OfficialLunchOut,108) ELSE CONVERT(CHAR(8),dbo.FN_Get1stLogout (T.CtrlNo, T.RecordDate, T.RecordDate), 108) END) MinAM,
			--End Here
			--LogIn PM
			CASE WHEN CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108) IS NULL 
			THEN CONVERT(CHAR(8),OT.OfficialLunchIn,108)
			ELSE 
				CASE WHEN CONVERT(CHAR(8),OT.OfficialLunchIn,108) >=  
				CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108)
				THEN CONVERT(CHAR(8),OT.OfficialLunchIn,108) 
				ELSE CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108) 
				END
			END TimeInPM,
			--End Here
			--TimeOutPM
			CASE WHEN CONVERT(CHAR(8),OT.OfficialTimeOut,108) <= 
			CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialTimeOut,108) WHEN CONVERT(CHAR(8),dbo.FN_Get2ndLogout (T.CtrlNo, 
			T.RecordDate, T.RecordDate), 108) >= 
			CONVERT(CHAR(8),OT.OfficialTimeOut,108) THEN CONVERT(CHAR(8),OT.OfficialTimeOut,108) ELSE 
			CONVERT(CHAR(8),dbo.FN_Get2ndLogout (T.CtrlNo, T.RecordDate, T.RecordDate), 108) END TimeOutPM,
			--End Here
			--Computation of Minutes PM
			DATEDIFF(mi,CASE WHEN CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108) IS NULL 
			THEN CONVERT(CHAR(8),OT.OfficialLunchIn,108)
			ELSE 
				CASE WHEN CONVERT(CHAR(8),OT.OfficialLunchIn,108) >=  
				CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108)
				THEN CONVERT(CHAR(8),OT.OfficialLunchIn,108) 
				ELSE CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108) 
				END
			END,
			CASE WHEN CONVERT(CHAR(8),OT.OfficialTimeOut,108) <= 
			CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialTimeOut,108) WHEN CONVERT(CHAR(8),dbo.FN_Get2ndLogout (T.CtrlNo, 
			T.RecordDate, T.RecordDate), 108) >= 
			CONVERT(CHAR(8),OT.OfficialTimeOut,108) THEN CONVERT(CHAR(8),OT.OfficialTimeOut,108) ELSE 
			CONVERT(CHAR(8),dbo.FN_Get2ndLogout (T.CtrlNo, T.RecordDate, T.RecordDate), 108) END) AS MinPM,
			--End Here
			--Total Minutes AM and PM
			DATEDIFF(mi,CASE WHEN CONVERT(CHAR(8),OT.OfficialTimeIn,108) >= 
			CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialTimeIn,108) ELSE CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, 
			T.RecordDate),108) END,
			CASE WHEN CONVERT(CHAR(8),OT.OfficialLunchOut,108) <= 
			CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialLunchOut,108) ELSE CONVERT(CHAR(8),dbo.FN_Get1stLogout 
			(T.CtrlNo, T.RecordDate, T.RecordDate), 108) END) + 
			DATEDIFF(mi,CASE WHEN CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108) IS NULL 
			THEN CONVERT(CHAR(8),OT.OfficialLunchIn,108)
			ELSE 
				CASE WHEN CONVERT(CHAR(8),OT.OfficialLunchIn,108) >=  
				CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108)
				THEN CONVERT(CHAR(8),OT.OfficialLunchIn,108) 
				ELSE CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108) 
				END
			END,
			CASE WHEN CONVERT(CHAR(8),OT.OfficialTimeOut,108) <= 
			CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialTimeOut,108) WHEN CONVERT(CHAR(8),dbo.FN_Get2ndLogout (T.CtrlNo, T.RecordDate, T.RecordDate), 108) >= 
			CONVERT(CHAR(8),OT.OfficialTimeOut,108) THEN CONVERT(CHAR(8),OT.OfficialTimeOut,108) ELSE 
			CONVERT(CHAR(8),dbo.FN_Get2ndLogout (T.CtrlNo, T.RecordDate, T.RecordDate), 108) END) [Total Minutes],
			--End Here
			--Total No. of Lates
			480 - (DATEDIFF(mi,CASE WHEN CONVERT(CHAR(8),OT.OfficialTimeIn,108) >= 
			CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialTimeIn,108) ELSE 
			
			case when CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, T.RecordDate),108)>CONVERT(CHAR(8),OT.OfficialLunchOut,108) then
				null ELSE
			
			CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 1, 
			T.RecordDate),108) END END,
			CASE WHEN CONVERT(CHAR(8),OT.OfficialLunchOut,108) <= 
			CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialLunchOut,108) ELSE CONVERT(CHAR(8),dbo.FN_Get1stLogout 
			(T.CtrlNo, T.RecordDate, T.RecordDate), 108) END) + 
			DATEDIFF(mi,CASE WHEN CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108) IS NULL 
			THEN CONVERT(CHAR(8),OT.OfficialLunchIn,108)
			ELSE 
				CASE WHEN CONVERT(CHAR(8),OT.OfficialLunchIn,108) >=  
				CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108)
				THEN CONVERT(CHAR(8),OT.OfficialLunchIn,108) 
				ELSE CONVERT(CHAR(8),dbo.FN_GetTime_In(T.CtrlNo, 2, T.RecordDate), 108) 
				END
			END,
			CASE WHEN CONVERT(CHAR(8),OT.OfficialTimeOut,108) <= 
			CONVERT(CHAR(8),dbo.FN_GetTime_Out(T.CtrlNo, 1, T.RecordDate),108) THEN
			CONVERT(CHAR(8),OT.OfficialTimeOut,108) WHEN CONVERT(CHAR(8),dbo.FN_Get2ndLogout (T.CtrlNo, T.RecordDate, T.RecordDate), 108) >= 
			CONVERT(CHAR(8),OT.OfficialTimeOut,108) THEN CONVERT(CHAR(8),OT.OfficialTimeOut,108) ELSE 
			CONVERT(CHAR(8),dbo.FN_Get2ndLogout (T.CtrlNo, T.RecordDate, T.RecordDate), 108) END)) AS [Total Lates]
			--End Here
			FROM dbo.Tbl_OfficialTime OT LEFT OUTER JOIN
			dbo.PR_TimeLogs T ON OT.CtrlNo = T.CtrlNo
			--WHERE dbo.fn_EmployeeNo (T.CTRLNo) ='070306-0477' AND (T.RecordDate BETWEEN CONVERT(DATETIME, 
			WHERE T.CTRLNo ='387' AND (T.RecordDate BETWEEN CONVERT(DATETIME, 
			'12-01-2013', 102) AND CONVERT(DATETIME, '12-6-2013', 102)) AND (T.LogType = 0)  
			AND dbo.fn_Days(OT.SchedIdx) = DATENAME(DW,T.RecordDate)  AND (OT.IS_Active = 1)AND 
			DATENAME(dw,T.RecordDate) <> 'Saturday' AND DATENAME(dw,T.RecordDate) <> 'Sunday' --and [Total Lates]<>0
			ORDER BY CONVERT(CHAR(10),T.RecordDate,101)



Is This A Good Question/Topic? 0
  • +

Replies To: Syntax error converting datetime to character string or integer

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3608
  • View blog
  • Posts: 12,399
  • Joined: 12-December 12

Re: Syntax error converting datetime to character string or integer

Posted 19 December 2013 - 12:20 AM

On which line of your posted code does the error occur?

The table schemas would also help.
Was This Post Helpful? 0
  • +
  • -

#3 marvinmartian  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 37
  • Joined: 07-October 09

Re: Syntax error converting datetime to character string or integer

Posted 19 December 2013 - 01:51 AM

View Postandrewsw, on 19 December 2013 - 12:20 AM, said:

On which line of your posted code does the error occur?

The table schemas would also help.



maybe the error is in line 26
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1