troubleshooting Question

converting varchar to int to extract records

Avatar of lisa_mc
lisa_mcFlag for United Kingdom of Great Britain and Northern Ireland asked on
DatabasesMicrosoft SQL Server 2005SQL
4 Comments3 Solutions393 ViewsLast Modified:
Hi everyone

I have attached a piece of code which I use to calculate students start_time, end_time, time late, time absent and so on for each day.  My aim is to find students who have been absent for longer than 4 hours per day (which is why I write all the records into the temp table #students then select from here which students have been absent 4 hours).  My problem is because absent is a varchar it wont let me compare to see if absent > 4 as I get the following error

Msg 8114, Level 16, State 5, Line 80
Error converting data type varchar to numeric.

Can anyone help me fix this?

Thanks
if object_id('tempdb..#students','U') is not null drop table #students

SELECT student_id, date, week_no, [start time], [end time], CONVERT(VARCHAR(5), [Late], 108) AS 'Late',
	CONVERT(VARCHAR(5), [Absent], 108) AS 'Absent'

into #students

FROM
(
	SELECT  student_id, Date,week_no, 
		MIN(CONVERT(datetime, start_time)) AS 'Start Time', 
		MAX(CONVERT(datetime, End_Time)) AS 'End Time',
		DateAdd(n, SUM(CASE WHEN attendance_type = 'LA' THEN late_minutes ELSE 0 END), 0) AS 'Late',
		DateAdd(n, SUM(CASE WHEN attendance_type IN ('AA', 'AB') THEN DATEDIFF(minute, start_time,  end_time) ELSE 0 END) 
			+ SUM(CASE WHEN attendance_type = 'LA' THEN late_minutes ELSE 0 END), 0) AS 'Absent'
		FROM allstudents

	GROUP BY student_id, Date, week_no
) X	 


select *

from #students

where Absent > 4
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros