Creating Days in a Year - Stored Procedure

I found this code below online and I am trying to modify it for my
needs. I want to pass a YEAR (Int) to the stored procedure and have it
create all the day in that passed year. With one tweak, If the day is
Sunday the "sStatus" field will equal "Closed" ELSE "Open". All
attempts come up with ERRORS. Also, I would like the sdate field to be
a DATE result. Little help please? I appreciate it.

USE colombo;

-- this table will store dates sequence
CREATE TABLE seq_dates
(
sdate DATETIME NOT NULL,
sStatus VARCHAR NULL,
);

DROP PROCEDURE IF EXISTS colombo.sp_init_dates;

CREATE PROCEDURE colombo.sp_init_dates
(IN p_fdate DATETIME, IN p_tdate DATETIME)
BEGIN
DECLARE v_thedate DATETIME;
DECLARE v_Status VARCHAR;

TRUNCATE TABLE colombo.seq_dates;

SET v_thedate = p_fdate;

WHILE (v_thedate < p_tdate) DO

IF (DayName(v_theDate) = "Sunday" {
SET v_Status = "Closed";
}else{
SET v_Status = "Open";
} // I know this CODE is wrong

-- insert dates squence into seq_dates table
INSERT INTO seq_dates (sdate, sStatus)
VALUES (v_thedate, v_Status);

-- go to the next day
SET v_thedate = DATE_ADD(v_thedate, INTERVAL 1 DAY);

END WHILE;

END;

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Creating Days in a Year - Stored Procedure

On Tue, 23 Feb 2010 14:49:01 -0800, donw@pointmade.net (Pointmade_Noah1) wrote:

>I found this code below online and I am trying to modify it for my
>needs. I want to pass a YEAR (Int) to the stored procedure and have it
>create all the day in that passed year. With one tweak, If the day is
>Sunday the "sStatus" field will equal "Closed" ELSE "Open". All
>attempts come up with ERRORS. Also, I would like the sdate field to be
>a DATE result. Little help please? I appreciate it.
>
>USE colombo;
>
>-- this table will store dates sequence
>CREATE TABLE seq_dates
>(
> sdate DATETIME NOT NULL,
> sStatus VARCHAR NULL,
>);
>
>DROP PROCEDURE IF EXISTS colombo.sp_init_dates;
>
>CREATE PROCEDURE colombo.sp_init_dates
>(IN p_fdate DATETIME, IN p_tdate DATETIME)
>BEGIN
>DECLARE v_thedate DATETIME;
>DECLARE v_Status VARCHAR;
>
>TRUNCATE TABLE colombo.seq_dates;
>
>SET v_thedate = p_fdate;
>
>WHILE (v_thedate < p_tdate) DO
>
>IF (DayName(v_theDate) = "Sunday" {
>SET v_Status = "Closed";
>}else{
>SET v_Status = "Open";
>} // I know this CODE is wrong
>
> -- insert dates squence into seq_dates table
> INSERT INTO seq_dates (sdate, sStatus)
> VALUES (v_thedate, v_Status);
>
> -- go to the next day
> SET v_thedate = DATE_ADD(v_thedate, INTERVAL 1 DAY);
>
>END WHILE;
>
>END;

You could probably do something cunning with the library functions mktime(), etc, but I
have two functions:

Abs_day ($date); which converts $date into an absolute day, with 1:1:1800 = day 0
and

Ymd ($abs_day); which converts an absolute day back into Y:M:D

For your purpose there is a lot of redundancy, but between them these functions provide
all the information you have asked for. Day zero was a Wednesday, so you can readily work
out the DOW for any arbitrary day.

I have tested these functions up to 2500, but no obvious upper limit).

// Convert date YYYY:MM:DD into absolute day; 1:1:1800 = 0, Wednesday

function abs_day ($u)
{
$days = array
(0 => array(0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334, 365, 375),
( 1 => array(0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335, 366, 375));
// 0 is normal year, 1 is leap year
$y = $u[0];
if (($y % 400) == 0) { $ly = 1; } // See if leap year
elseif (($y % 100) == 0) { $ly = 0; }
elseif (($y % 4) == 0) { $ly = 1; }
else { $ly = 0; }

$y -= 1800;
$c = (int) (($y - 0.99)/100);
$c = (int) ($c * 0.75 +0.3); // Fiddle factor for centuries (most are not leap
years)
$abs_day = (int) (($y) * 365.25 - $c - 0.1);
$d = (int) $u[1];
$abs_day += $u[2]-1 + $days[$ly][$d];
return $abs_day;
}

// Converts abs day to YMD.

function ymd ($day)
{
//echo '

Stdu_408: Day = '.$day.'