Error
I am trying to create a function that returns the business hours between two dates. I have been getting this error code: 1064 (Syntax error)
Here's My code:
CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`(StartDate Date, FinishDate Date)
BEGIN
DECLARE Temp INTEGER;
DECLARE FirstDay DATE;
DECLARE LastDay DATE;
DECLARE StartTime TIME;
DECLARE FinishTime TIME;
DECLARE WorkStart TIME;
DECLARE WorkFinish TIME;
DECLARE DailyWorkTime BIGINT;
DECLARE CurrentDate DATE;
DECLARE LastDate DATE;
SET Temp=0;
SET FirstDay = CONVERT(DATE, StartDate, 112);
SET LastDay = CONVERT(DATE, FinishDate, 112);
SET StartTime = CONVERT(TIME, StartDate);
SET FinishTime = CONVERT(TIME, FinishDate);
SET WorkStart = '09:30';
SET WorkFinish = '17:30';
SET DailyWorkTime = DATEDIFF(MINUTE, WorkStart, WorkFinish);
IF (StartTime<WorkStart)
BEGIN
SET StartTime = WorkStart
END
IF (FinishTime>WorkFinish)
BEGIN
SET FinishTime=WorkFinish
END
IF (FinishTime<WorkStart)
BEGIN
SET FinishTime=WorkStart
END
IF (StartTime>WorkFinish)
BEGIN
SET StartTime = WorkFinish
END
SET CurrentDate = FirstDay
SET LastDate = LastDay
WHILE(CurrentDate<=LastDate)
BEGIN
IF (DATEPART(dw, CurrentDate)!=1 AND DATEPART(dw, CurrentDate)!=7)
BEGIN
IF (CurrentDate!=FirstDay) AND (CurrentDate!=LastDay)
BEGIN
SET Temp = Temp + DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (CurrentDate=FirstDay) AND (CurrentDate!=LastDay)
BEGIN
SET Temp = Temp + DATEDIFF(MINUTE, StartTime, WorkFinish)
END
ELSE IF (CurrentDate!=FirstDay) AND (CurrentDate=LastDay)
BEGIN
SET Temp = Temp + DATEDIFF(MINUTE, WorkStart, FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (CurrentDate=FirstDay) AND (CurrentDate=LastDay)
BEGIN
SET Temp = DATEDIFF(MINUTE, StartTime, FinishTime)
END
END
SET CurrentDate = DATEADD(day, 1, CurrentDate)
END
-- Return the result of the function
IF Temp<0
BEGIN
SET Temp=0
END
RETURN Temp
END;
Error Message is at line no 15:
13. SET Temp=0;
14. SET FirstDay = CONVERT(DATE, StartDate, 112);
15. SET LastDay = CONVERT(DATE, FinishDate, 112);
16. SET StartTime = CONVERT(TIME, StartDate);
As this is the forst time i am writing a mysql function, I couldn't figure out what is wrong with the code.