Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

problem with stored procedure

Posted on 2010-09-14
10
Medium Priority
?
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 33670434
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
ID: 33670439
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
ID: 33670445
oops..  ignore my answer.   103 and 3 is the same error
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 19

Accepted Solution

by:
elimesika earned 1000 total points
ID: 33670473
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
ID: 33670680
thanks everyone for the replies

Just testing it now will post back my results
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 33671190
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 1000 total points
ID: 33672719
>>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
ID: 33680287
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
ID: 33682357
>>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
ID: 33690240
thanks guys
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

610 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