MySQL Forums
Forum List  »  Stored Procedures

Error
Posted by: Sijan Bhattarai
Date: May 08, 2018 12:52AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Error
1050
May 08, 2018 12:52AM
454
May 08, 2018 10:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.