Jump to content



Recommended Posts

I would like to create a stored routine for MySQL that figures out the number of business or working days for a month (Working Days are Monday thru Friday). I have not created the procedure portion of it yet as I want to ensure that the code works first. Please note that there is a function called first_day. This is a function provided by my hosting provider. I also have another function for this that will figure out the first day of the month if I didn't have it from the hosting company. It is not currently used and can be removed for testing purposes. I am testing this through phpmyadmin SQL Query. phpmyadmin uses the mysql extension and not the mysqli extension processing if that makes a difference. (The hosting company refuses to update it). It's a syntax error however I don't know what the syntax error is. All it tells me is: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE(@daycount < @totaldays) DO IF (WEEKDAY(@checkweekday) < 6) THEN ' at line 2 My Syntax Error is in the following:

	WHILE(@daycount < @totaldays) DO		  IF (WEEKDAY(@checkweekday) < 6) THEN

My Code:

	  SELECT MONTH(CURDATE()) INTO @curmonth;	  SELECT MONTHNAME(CURDATE()) INTO @curmonthname;	  SELECT DAY(LAST_DAY(CURDATE())) INTO @totaldays;	  SELECT FIRST_DAY(CURDATE()) INTO @checkweekday;	  SELECT DAY(@checkweekday) INTO @checkday;	  SET @daycount = 0;	  SET @workdays = 0; 	BEGIN	  WHILE(@daycount < @totaldays) DO		  IF (WEEKDAY(@checkweekday) < 6) THEN			SET @workdays = @workdays+1;		  END IF;		  SET @daycount = @daycount+1;		  SELECT ADDDATE('@checkweekday', INTERVAL 1 DAY) INTO @checkweekday;	  END WHILE;	END;	SELECT @workdays;

I receive the same error with the following bit of code so it probably has something to do with this:

	SET @workdays = 0;	IF (WEEKDAY('2013-06-13') < 6) THEN	  SET @workdays = @workdays+1;	END IF;	SELECT @workdays;

Is someone able to assist?

Edited by Spectacular Stuff
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...