On Tue, 23 Feb 2010 14:49:01 -0800, donw@xxxxxxxxxxxxx (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 '<h5>Stdu_408: Day = '.$day.'</h5>';
$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));
$u = false;
$c = (int)(($day + 0.6)/36524.25); // No of centuries
$ly = 0; if ((($c + 2) % 4) == 0) { $ly = 1; } // See if leap year
$d0 = $day - (int) ($c * 36524.25 + 0.3); // Calculate remaining days
$yy = (int) (($d0 -$ly + 1.1)/365.25); // Calculate year
$d2 = $d0 +1 - (int) ($yy * 365.25 + $ly - 0.125); // & remaining days (again!)
if ($yy != 0)
{
if (($yy % 4) == 0) { $ly = 1; } else { $ly = 0; } // See if leap year
}
$u[0] = 100 * $c + $yy +1800; // Calc calendar year
$i = 1; $j = 0;
while ($d2 > $days[$ly][$i] && $i < 14) { $i++; } // and month & day
$d2 -= $days[$ly][$i-1];
$u[1] = $i; $u[2] = $d2;
return $u;
}
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
