problem with stored procedure

Hi everyone

I have a problem with the code shown below.  It takes about 9 minutes to run this stored procedure so its very fustrating that I have to wait that long to say its wrong.

The error that I am gettin is

Msg 242, Level 16, State 3, Procedure nrc_LISA_info_for_e-registers, Line 90
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

(1 row(s) affected)

I think it is something to do with the starttime and endtime in the temporary table then storing them again in my stored procedure.  In the temporary table they are datetime but in my select statement I have converted these to char to get a 5 digit output eg 10:45.  I have declared these as datetime in the stored procedure table maybe this is what is causing the problem.  I tried storing them as varchar but I am getting months of the year as results.  Also the date in the table could also be a problem as I am trying to convert datetime to just output of date eg 01/05/2010 instead of 01/05/2010 00:00

Any help greatly appreciated

Thanks

USE [nicisreports]
GO
/****** Object:  StoredProcedure [dbo].[nrc_LISA_info_for_e-registers]    Script Date: 09/14/2010 09:41:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[nrc_LISA_info_for_e-registers]

@pAcadPeriod char(5)
AS

SET NOCOUNT ON

-- temporary table used to store the results from the cross join of table
-- sttrgprf and nrc_weekDates.
-- cuts execution time from hours to minutes

DECLARE @week_table TABLE
( 
	acad_period NVARCHAR(10),
	register_id NVARCHAR(64), 
	register_group NVARCHAR(64),
	week_no NVARCHAR(64),
	day_of_week NVARCHAR(64),
	dept_code NVARCHAR(10),
	starttime datetime,
	endtime datetime
)

INSERT INTO @week_table 
( 
	acad_period,
	register_id,
	register_group,
	week_no,
	day_of_week,
	dept_code,
	starttime,
	endtime
)

SELECT DISTINCT y.acad_period, y.register_id, y.register_group, z.week_no, y.day_of_week, y.dept_code, y.start_time, y.end_time

FROM qlsdat..sttrgprf y
cross join dbo.nrc_weekDates z

WHERE y.acad_period = @pAcadPeriod

ORDER BY 1,2,4

--SELECT * FROM @week_table

/* Declare local variables */

/* Create tables */
if exists (select * from nicisReports.dbo.sysobjects
where id = object_id(N'[nicisReports].[dbo].[nrc_LISA_info_for_e_registers_tb]'))
  delete from nrc_LISA_info_for_e_registers_tb where acad_period=@pAcadPeriod 
else
    CREATE TABLE [dbo].[nrc_LISA_info_for_e_registers_tb] (
    acad_period NVARCHAR(10),
	register_id NVARCHAR(64),
	register_group NVARCHAR(64),
	par_aos_sess NVARCHAR(64),
	par_full_desc NVARCHAR(64),
	course_coordinator NVARCHAR(64),
	child_aos_sess NVARCHAR(64),
	child_full_desc NVARCHAR(64),
	day_num NVARCHAR(64),
	day NVARCHAR(64),
	week_no NVARCHAR(64),
	date datetime,
	start_time datetime,
	end_time datetime,
	dept_code NVARCHAR(64),
	dept_desc NVARCHAR(64),
	campus_id NVARCHAR(64),
	campus_desc NVARCHAR(64),
	mod_staff_code NVARCHAR(64),
	mod_staff_title NVARCHAR(10),
	mod_staff_name NVARCHAR(64),
	stud_id NVARCHAR(64),
	stud_title NVARCHAR(10),
	stud_fam_name NVARCHAR(64),
	stud_surname NVARCHAR(64),
	absence_code NVARCHAR(64),
	attend_mode NVARCHAR(64),
	lect_staff_code NVARCHAR(64),
	reg_staff_code NVARCHAR(64),
	reg_title NVARCHAR(10),
	reg_name NVARCHAR(64),
	register_mark_date datetime,
	day_diff NVARCHAR(64)
    )

insert into nrc_LISA_info_for_e_registers_tb
SELECT	distinct a.acad_period, a.register_id, a.register_group, 
		ltrim(rtrim(coalesce(d.aos_code, c.aos_code))) +'/'+  ltrim(rtrim(coalesce(d.aos_period, c.aos_period))) as 'par_aos_sess',
		f.full_desc, t.familiar_name as "Course Co-ordinator",
		ltrim(rtrim(c.aos_code))+'/'+ ltrim(rtrim(c.aos_period)) as 'Child_aos_sess', 
		g.full_desc as "Module Desc", 
		a.day_of_week as "Day Num", 
		(case a.day_of_week	when 1 then 'Mon'
							when 2 then 'Tue'
							when 3 then 'Wed'
							when 4 then 'Thu'
							when 5 then 'Fri'
							when 6 then 'Sat'
							when 7 then 'Sun' else null end) as 'Day',
		a.week_no,
		convert (varchar(10),q.date,3) as 'date',
		convert(varchar(5),a.starttime,108) as 'start time',
		convert(varchar(5),a.endtime,108) as 'end time', 
		a.dept_code, b.description, 
		i.campus_id, l.camp_desc,
		o.staff_code as 'mod lect', o.title, o.familiar_name as 'lect name', 
		e.student_id, m.title, m.famliar_name as 'student name', m.surname as 'student surname',p.absence_code, s.attend_mode,
		o.staff_code as 'mod lect' , v.staff_code as 'reg marker', v.title, v.familiar_name as 'reg marker name', convert (varchar,w.date_time,3) as 'register_mark_date',
		datediff(day, q.date, w.date_time) as 'day_diff'

Select allOpen in new window