I have the below stored procedure which used to work fine in sql server 2000, however we have just upgraded to sql server 2005 and now my stored procedure wont work, i keep getting a convert error but not sure why as i havent changed anything in the code, so im thinking this may be a problem with the table which i had to recreat. I think when i was usign 2000 i only hade the date being saved in the start date box, but now have date and time, could this be the problem, or is there something wrong with the below code.
Thanks
Thanks
Code:
ALTER PROCEDURE [dbo].[sp_EngineerTimeslots]
@STARTDATE DATETIME,
@PRIORITY INT
AS
DECLARE @SQLTIMESLOTS VARCHAR(3000)
SET NOCOUNT ON
SELECT TOP 100 PERCENT dbo.tbl_engineer.ENGINEER AS Engineer, NULL AS BDate, dbo.tbl_engineer.STARTTIME AS FTime, dbo.tbl_engineer.ENDTIME AS ToTime
FROM dbo.Schedule RIGHT OUTER JOIN dbo.tbl_engineer ON dbo.Schedule.Engineer = dbo.tbl_engineer.ENGINEER
WHERE (dbo.tbl_engineer.ACTIVE = 1) AND (dbo.Schedule.Engineer IS NULL)
UNION
SELECT TOP 100 PERCENT Schedule.Engineer AS Engineer , Schedule.BookedDate AS BDate, Schedule.FromTime AS FTime, ISNULL(Schedule.ToTime, engineer.ENDTIME) AS ToTime
FROM (SELECT Engineer, BookedDate, ToTime AS FromTime,
(SELECT MIN(fromTime)
FROM schedule x
WHERE x.engineer = s1.engineer AND x.bookedDate = s1.bookedDate AND x.fromTime >= s1.ToTime) AS ToTime
FROM schedule s1
WHERE bookedDate <= DATEADD(HOUR, @PRIORITY, CONVERT(DATETIME, @STARTDATE, 103))
UNION ALL
SELECT e2.Engineer, s2.BookedDate, e2.StartTime AS FromTime, MIN(s2.FromTime) AS ToTime
FROM tbl_engineer e2 INNER JOIN
schedule s2 ON s2.engineer = e2.engineer
WHERE s2.bookedDate <= DATEADD(HOUR, @PRIORITY, CONVERT(DATETIME, @STARTDATE, 103))
GROUP BY e2.Engineer, s2.BookedDate, e2.StartTime
HAVING MIN(s2.FromTime) > e2.StartTime) Schedule INNER JOIN
dbo.tbl_engineer engineer ON engineer.ENGINEER = Schedule.Engineer
WHERE (Schedule.FromTime <> Schedule.ToTime) OR
(Schedule.FromTime < engineer.ENDTIME) AND (Schedule.ToTime IS NULL)
