|
|
Stored procedure:
USE colombo;
-- this table will store dates sequence
CREATE TABLE seq_dates
(
sdate DATETIME NOT NULL,
sy INT NULL,
sm INT NULL,
sd INT NULL,
day_no INT NULL
);
/* =====================================================
Author: amper
Create date: 2007-11-20
Description: Create dates squence table in sp
======================================================= */
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;
TRUNCATE TABLE colombo.seq_dates;
SET v_thedate = p_fdate;
WHILE (v_thedate < p_tdate) DO
-- insert dates squence into seq_dates table
INSERT INTO seq_dates (sdate, sy, sm, sd, day_no)
VALUES (v_thedate, YEAR(v_thedate),
MONTH(v_thedate),
DAY(v_thedate),
DAYOFYEAR(v_thedate));
-- go to the next day
SET v_thedate = DATE_ADD(v_thedate, INTERVAL 1 DAY);
END WHILE;
END;
|
Run stored procedure and see results:
CALL colombo.sp_init_dates ('2007-12-29','2008-01-03');
SELECT * FROM colombo.seq_dates;
|
The results will be as follows:
|
sdate | sy | sm | sd | day_no |
| 20071229 00:00:00 | 2007 | 12 | 29 | 363 |
| 20071230 00:00:00 | 2007 | 12 | 30 | 364 |
| 20071231 00:00:00 | 2007 | 12 | 31 | 365 |
| 20080101 00:00:00 | 2007 | 1 | 1 | 1 |
| 20080102 00:00:00 | 2008 | 1 | 1 | 2 |
|