Solved

problem with stored procedure

Posted on 2010-09-14
10
255 Views
Last Modified: 2012-05-10
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'

Open in new window

0
Comment
Question by:lisa_mc
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 22

Expert Comment

by:Om Prakash
Comment Utility
Use
convert (varchar(10),q.date,101) as 'date'
instead of
convert (varchar(10),q.date,3) as 'date',

Line 112
0
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
your problem is with line 112 in your above given code

            convert (varchar(10),q.date,3) as 'date'

you should try 103 instead of 3

            convert (varchar(10),q.date,103) as 'date'
0
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
oops..  ignore my answer.   103 and 3 is the same error
0
 
LVL 19

Accepted Solution

by:
elimesika earned 250 total points
Comment Utility
try this
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',
		Left(convert(varchar(8),a.starttime,108),5) as 'start time',
		Left(convert(varchar(8),a.endtime,108),5) 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'

Open in new window

0
 
LVL 3

Author Comment

by:lisa_mc
Comment Utility
thanks everyone for the replies

Just testing it now will post back my results
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Author Comment

by:lisa_mc
Comment Utility
Hi elimesika

I tried the code

Left(convert(varchar(8),a.starttime,108),5) as 'start time',
Left(convert(varchar(8),a.endtime,108),5) as 'end time',

it compiles but start time and end time is still stored as 01/01/1900 09:00:00

in refence to the date fields

convert (varchar(8),q.date,3) as 'date',
convert (varchar(8),w.date_time,3) as 'register_mark_date'

they will only compile if I change to

q.date as 'date'
w.date_time as 'register_mark_date'  

but the output I am getting is 11/01/2010 00:00:00

Is there any way of changing the time to 5 digits eg 09:45
and the date to 10 digits eg 01/01/2010

when i used this in a script it works is it just that it cannot be done in a stored procedure
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 250 total points
Comment Utility
>>it compiles but start time and end time is still stored as 01/01/1900 09:00:00<<
The data is stored like that because both starttime and enddtime are DATETIME datatype. If you want to display it as 'HH:MM" then you do it in your select when you query the nrc_LISA_info_for_e_registers_tb table like this
select convert(varchar(5), [start time], 108), convert(varchar(5), [end time], 108) .... from nrc_LISA_info_for_e_registers_tb

Now if you still insist on storing hh:mm literally, then you will have change the datatype to varchar in your table definition.
create table nrc_LISA_info_for_e_registers_tb ( .... starttime varchar(5), endtime varchar(5) ....)
and then you can run your insert like this
insert nrc_LISA_info_for_e_registers_tb select  ..........   convert(varchar(5), a.starttime, 108) as [start time], convert varchar(5), a.endtime, 108)
Having said that, I DO NOT recommend the latter approach. date and time should be stored as datetime datatype.  So my first recommendation is the way to go.
>>they will only compile if I change to

q.date as 'date'
w.date_time as 'register_mark_date'  <<
Of course if q.date and w.date_time are already datetime format, why would you bother doing a convert?? Again if you want to display DD/MM/YYYY format, you do it in your select query, when you retrieve your data, not when you insert it.
select convert(varchar, [date], 103), convert(varchar, register_mark_date, 108) from nrc_LISA_info_for_e_registers_tb

0
 
LVL 3

Author Comment

by:lisa_mc
Comment Utility
ideally I wanted to store the values like this when I run my stored procedure but I take it - it would be easier to just do this when I create a view or query the stored procedure
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
>>ideally I wanted to store the values like this <<
It's up to you, but let me tell you that's not good practice, because manipulating date/time when the datatype is varchar is quite problematic in SQL.
0
 
LVL 3

Author Closing Comment

by:lisa_mc
Comment Utility
thanks guys
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now