| DML | //
// Demonstration of how to use the various MaxDB functions
//
// -> server supplied functions
//
// Precedence Rules
// Expressions within parentheses are done first, then prefix operators (e.g. -1),
// then multiplication and division, then addition and subtraction.
// When more operations of equal precedence are
// together (e.g. 1 * 5 / 4) they are done from left to right.
//
// ,555 + -22 / (12 - 3) * 66 AS dec4
// ^ ^ ^ ^ ^
// 5th 2nd 3rd 1st 4th
//
SELECT (12 - 3) AS int1
,-22 / (12 - 3) AS dec2
,-22 / (12 - 3) * 66 AS dec3
,555 + -22 / (12 - 3) * 66 AS dec4
FROM dual
//
// Equivalent DB2 command:
// SELECT (12 - 3) AS int1
// ,-22 / (12 - 3) AS int2
// ,-22 / (12 - 3) * 66 AS int3
// ,555 + -22 / (12 - 3) * 66 AS int4
// FROM sysibm.sysdummy1;
//
// Result from db2
//
// INT1 INT2 INT3 INT4
// ----------- ----------- ----------- -----------
// 9 -2 -132 423
//
// Equivalent MaxDB to DB2 command:
SELECT (12 - 3) AS int1
,FIXED(-22 / (12 - 3)) AS int2
,FIXED(-22 / (12 - 3) * 66) AS int3
,555 + FIXED(-22 / (12 - 3)) * 66 AS int4
FROM dual
//
// TRUNC versus ROUND
SELECT (12 - 3) AS int1
,-22 / (12 - 3) AS dec2a
,FLOOR(-22 / (12 - 3)) AS dec2b
,FIXED(-22 / (12 - 3) * 66,5,2) AS dec3
,TRUNC(555 + -22 / (12 - 3) * 66,2) AS dec4
,ROUND(555 + -22 / (12 - 3) * 66,2) AS dec5
FROM dual
//
// See result column dec1 in comparison to int2
// 3 DIV 0,5 leads to an error message
SELECT 3 / 1 AS dec1
,TRUNC(3/1,2) AS dec2
,TRUNC(3/1,0) AS int1
,3 DIV 1 AS int2
,3 MOD 2 AS int3
,3 MOD 3 AS int4
FROM dual
//
|
| Result | > SELECT (12 - 3) AS int1 ,-22 / (12 - 3) AS dec2 ,-22 / (12 - 3) * 66 AS dec3 ,555 + -22 / (12 - 3) * 66 AS dec4 FROM dual
> | INT1 | DEC2 | DEC3 | DEC4 |
> | -------- | ------------------------------------------------------- | ---------------------------------------------- | ---------------------------------------------- |
> | 9 | -2.444444444444444444444444444444444444 | -161.33333333333333333333333333333333333 | 393.66666666666666666666666666666666667 |
> SELECT (12 - 3) AS int1 ,FIXED(-22 / (12 - 3)) AS int2 ,FIXED(-22 / (12 - 3) * 66) AS int3 ,555 + FIXED(-22 / (12 - 3)) * 66 AS int4 FROM dual
> | INT1 | INT2 | INT3 | INT4 |
> | -------- | ------------------------------------------------------ | ------------------------------------------------------ | ---------------------------------------------- |
> | 9 | -2 | -161 | 423 |
> SELECT (12 - 3) AS int1 ,-22 / (12 - 3) AS dec2a ,FLOOR(-22 / (12 - 3)) AS dec2b ,FIXED(-22 / (12 - 3) * 66,5,2) AS dec3 ,TRUNC(555 + -22 / (12 - 3) * 66,2) AS dec4 ,ROUND(555 + -22 / (12 - 3) * 66,2) AS dec5 FROM dual
> | INT1 | DEC2A | DEC2B | DEC3 | DEC4 | DEC5 |
> | -------- | ------------------------------------------------------- | ------------------------------------------------------ | ----------- | ---------------------------------------------- | ---------------------------------------------- |
> | 9 | -2.444444444444444444444444444444444444 | -3 | -161.33 | 393.66 | 393.67 |
> SELECT 3 / 1 AS dec1 ,TRUNC(3/1,2) AS dec2 ,TRUNC(3/1,0) AS int1 ,3 DIV 1 AS int2 ,3 MOD 2 AS int3 ,3 MOD 3 AS int4 FROM dual
> | DEC1 | DEC2 | INT1 | INT2 | INT3 | INT4 |
> | ------------------------------------------------------- | --------- | ------------------------------------------------------ | -------- | -------- | -------- |
> | 3.0000000000000000000000000000000000000 | 3.00 | 3 | 3 | 1 | 0 |
|
| |
| DML | //
// Demonstration of how to use the various MaxDB functions
//
// -> system supplied functions (special register)
SELECT ROWNO
,NULL
,USER
,USERGROUP
,SYSDBA
,CURRENT_SCHEMA
,UID
,DATE
,TIME
,TIMESTAMP
,UTCDATE
,TIMEZONE
,UTCDIFF
,TRUE
,FALSE
,TRANSACTION
FROM dual
//
// -> one user defined function (an example to generate random data)
DROP FUNCTION RAND
//
CREATE FUNCTION RAND (weight SMALLINT) RETURNS FIXED(38,37) AS
VAR randval FIXED(38,37);
SELECT ((134775818 * MICROSECOND(TIMESTAMP) + :weight) MOD 231) / 230
INTO :randval
FROM dbadmin.dual;
RETURN randval;
//
// -> embedding UDF in SQL
SELECT
RAND(1) AS r0
, FIXED(RAND(1)*10,1) AS r1
, FIXED(RAND(1)*100,2) AS r2
, FIXED(RAND(1)*1000,3) AS r3
, FIXED(RAND(1)*10000,4) AS r4
, FIXED(RAND(1)*100000,5) AS r5
, FIXED(RAND(1)*1000000,6) AS r6
, FIXED(RAND(1)*10000000,7) AS r7
, FIXED(RAND(1)*100000000,8) AS r8
, FIXED(RAND(1)*1000000000,9) AS r9
, FIXED(RAND(1)*10000000000,10) AS r10
FROM dual
//
|
| Result | > SELECT ROWNO ,NULL ,USER ,USERGROUP ,SYSDBA ,CURRENT_SCHEMA ,UID ,DATE ,TIME ,TIMESTAMP ,UTCDATE ,TIMEZONE ,UTCDIFF ,TRUE ,FALSE ,TRANSACTION FROM dual
> | ROWNO | EXPRESSION1 | EXPRESSION2 | EXPRESSION3 | EXPRESSION4 | EXPRESSION5 | EXPRESSION6 | EXPRESSION7 | EXPRESSION8 | EXPRESSION9 | EXPRESSION10 | EXPRESSION11 | EXPRESSION12 | EXPRE | EXPRE | EXPRESSION15 |
> | ---------------- | ------------------------ | -------------------------------- | -------------------------------- | -------------------------------- | -------------------------------- | ------------------ | ----------- | ----------- | -------------------------- | -------------------------- | ------------------- | -------------------- | ----- | ----- | -------------------------- |
> | 1 | ? | MONA | MONA | DBADMIN | MONA | 29 | 2008-02-17 | 10:44:27 | 2008-02-17 10:44:27.953000 | 2008-02-17 09:44:27.953000 | 10000 | 1.00 | TRUE | FALSE | 0x0000000000BB |
> DROP FUNCTION RAND
> CREATE FUNCTION RAND (weight SMALLINT) RETURNS FIXED(38,37) AS VAR randval FIXED(38,37); SELECT ((134775818 * MICROSECOND(TIMESTAMP) + :weight) MOD 231) / 230 INTO :randval FROM dbadmin.dual; RETURN randval;
> SELECT RAND(1) AS r0 , FIXED(RAND(1)*10,1) AS r1 , FIXED(RAND(1)*100,2) AS r2 , FIXED(RAND(1)*1000,3) AS r3 , FIXED(RAND(1)*10000,4) AS r4 , FIXED(RAND(1)*100000,5) AS r5 , FIXED(RAND(1)*1000000,6) AS r6 , FIXED(RAND(1)*10000000,7) AS r7 , FIXED(RAND(1)*100000000,8) AS r8 , FIXED(RAND(1)*1000000000,9) AS r9 , FIXED(RAND(1)*10000000000,10) AS r10 FROM dual
> | R0 | R1 | R2 | R3 | R4 | R5 | R6 | R7 | R8 | R9 | R10 |
> | ------------------------------------------------------- | ------ | ------ | ------- | -------- | ---------- | ----------- | ------------ | -------------- | --------------- | ---------------- |
> | 0.6695652173913043478261000000000000000 | 7 | 67 | 670 | 6696 | 66957 | 669565 | 6695652 | 66956522 | 669565217 | 6695652174 |
|
| |
| DML | //
// Demonstration of how to use the various MaxDB functions
//
// Use of UDF RAND and recursive SQL to generate 10 rows of test data
DECLARE test_data CURSOR FOR WITH RECURSIVE td
(levl, num1, num2, num3, r0, r1, r2, r3, r4, r5, r6, r7, r8, r9, r10) AS
(SELECT 1
, 1
, 0.5
, 0.5
, RAND(1) AS r0
, FIXED(RAND(1)*10,1) AS r1
, FIXED(RAND(1)*100,2) AS r2
, FIXED(RAND(1)*1000,3) AS r3
, FIXED(RAND(1)*10000,4) AS r4
, FIXED(RAND(1)*100000,5) AS r5
, FIXED(RAND(1)*1000000,6) AS r6
, FIXED(RAND(1)*10000000,7) AS r7
, FIXED(RAND(1)*100000000,8) AS r8
, FIXED(RAND(1)*1000000000,9) AS r9
, FIXED(RAND(1)*10000000000,10) AS r10
FROM dual
UNION ALL
SELECT levl + 1
, num1 * 10
, FIXED((num2 * 1.2),10,8)
, FIXED(num3 * 1.2,10,8) * -1
, RAND(levl + 1) AS r0
, FIXED(RAND(levl + 1)*10,1) AS r1
, FIXED(RAND(levl + 1)*100,2) AS r2
, FIXED(RAND(levl + 1)*1000,3) AS r3
, FIXED(RAND(levl + 1)*10000,4) AS r4
, FIXED(RAND(levl + 1)*100000,5) AS r5
, FIXED(RAND(levl + 1)*1000000,6) AS r6
, FIXED(RAND(levl + 1)*10000000,7) AS r7
, FIXED(RAND(levl + 1)*100000000,8) AS r8
, FIXED(RAND(levl + 1)*1000000000,9) AS r9
, FIXED(RAND(levl + 1)*10000000000,10) AS r10
FROM td
WHERE levl < 10
)
SELECT *
FROM td
FOR REUSE /* the result set "td" is kept until db session ends */
//
// Other way to define test_data:
SELECT -2.4,'ABCDEF','1996-04-22-23.58.58.123456' FROM dual UNION ALL
SELECT +0.0,'ABCD ' ,'1996-08-15-15.15.15.151515' FROM dual UNION ALL
SELECT +1.8,'AB ' ,'0001-01-01-00.00.00.000000' FROM dual
//
|
| Result | > DECLARE test_data CURSOR FOR WITH RECURSIVE td (levl, num1, num2, num3, r0, r1, r2, r3, r4, r5, r6, r7, r8, r9, r10) AS (SELECT 1 , 1 , 0.5 , 0.5 , RAND(1) AS r0 , FIXED(RAND(1)*10,1) AS r1 , FIXED(RAND(1)*100,2) AS r2 , FIXED(RAND(1)*1000,3) AS r3 , FIXED(RAND(1)*10000,4) AS r4 , FIXED(RAND(1)*100000,5) AS r5 , FIXED(RAND(1)*1000000,6) AS r6 , FIXED(RAND(1)*10000000,7) AS r7 , FIXED(RAND(1)*100000000,8) AS r8 , FIXED(RAND(1)*1000000000,9) AS r9 , FIXED(RAND(1)*10000000000,10) AS r10 FROM dual UNION ALL SELECT levl + 1 , num1 * 10 , FIXED((num2 * 1.2),10,8) , FIXED(num3 * 1.2,10,8) * -1 , RAND(levl + 1) AS r0 , FIXED(RAND(levl + 1)*10,1) AS r1 , FIXED(RAND(levl + 1)*100,2) AS r2 , FIXED(RAND(levl + 1)*1000,3) AS r3 , FIXED(RAND(levl + 1)*10000,4) AS r4 , FIXED(RAND(levl + 1)*100000,5) AS r5 , FIXED(RAND(levl + 1)*1000000,6) AS r6 , FIXED(RAND(levl + 1)*10000000,7) AS r7 , FIXED(RAND(levl + 1)*100000000,8) AS r8 , FIXED(RAND(levl + 1)*1000000000,9) AS r9 , FIXED(RAND(levl + 1)*10000000000,10) AS r10 FROM td WHERE levl < 10 ) SELECT * FROM td FOR REUSE /* the result set "td" is kept until db session ends */
> | LEVL | NUM1 | NUM2 | NUM3 | R0 | R1 | R2 | R3 | R4 | R5 | R6 | R7 | R8 | R9 | R10 |
> | ---------------------------------------------- | ---------------------------------------------- | ----------------- | ---------------------------------------------- | ------------------------------------------------------- | ------ | ------ | ------- | -------- | ---------- | ----------- | ------------ | -------------- | --------------- | ---------------- |
> | 10 | 1000000000 | 2.57989018 | -2.57989018 | 0.6260869565217391304348000000000000000 | 6 | 63 | 626 | 6261 | 62609 | 626087 | 6260870 | 62608696 | 626086957 | 6260869565 |
> | 9 | 100000000 | 2.14990848 | 2.14990848 | 0.6217391304347826086957000000000000000 | 6 | 62 | 622 | 6217 | 62174 | 621739 | 6217391 | 62173913 | 621739130 | 6217391304 |
> | 8 | 10000000 | 1.79159040 | -1.7915904 | 0.6173913043478260869565000000000000000 | 6 | 62 | 617 | 6174 | 61739 | 617391 | 6173913 | 61739130 | 617391304 | 6173913043 |
> | 7 | 1000000 | 1.49299200 | 1.492992 | 0.6130434782608695652174000000000000000 | 6 | 61 | 613 | 6130 | 61304 | 613043 | 6130435 | 61304348 | 613043478 | 6130434783 |
> | 6 | 100000 | 1.24416000 | -1.24416 | 0.6086956521739130434783000000000000000 | 6 | 61 | 609 | 6087 | 60870 | 608696 | 6086957 | 60869565 | 608695652 | 6086956522 |
> | 5 | 10000 | 1.03680000 | 1.0368 | 0.6043478260869565217391000000000000000 | 6 | 60 | 604 | 6043 | 60435 | 604348 | 6043478 | 60434783 | 604347826 | 6043478261 |
> | 4 | 1000 | 0.86400000 | -0.864 | 0.6000000000000000000000000000000000000 | 6 | 60 | 600 | 6000 | 60000 | 600000 | 6000000 | 60000000 | 600000000 | 6000000000 |
> | 3 | 100 | 0.72000000 | 0.72 | 0.5217391304347826086957000000000000000 | 5 | 52 | 522 | 5217 | 52174 | 521739 | 5217391 | 52173913 | 595652174 | 5956521739 |
> | 2 | 10 | 0.60000000 | -0.6 | 0.5173913043478260869565000000000000000 | 5 | 52 | 517 | 5174 | 51739 | 517391 | 5173913 | 51739130 | 517391304 | 5173913043 |
> | 1 | 1 | 0.50000000 | 0.5 | 0.5130434782608695652174000000000000000 | 5 | 51 | 513 | 5130 | 51304 | 513043 | 5130435 | 51304348 | 513043478 | 5130434783 |
> SELECT -2.4,'ABCDEF','1996-04-22-23.58.58.123456' FROM dual UNION ALL SELECT +0.0,'ABCD ' ,'1996-08-15-15.15.15.151515' FROM dual UNION ALL SELECT +1.8,'AB ' ,'0001-01-01-00.00.00.000000' FROM dual
> | EXPRESSION1 | EXPRESSION2 | EXPRESSION3 |
> | ------------------- | ----------- | -------------------------- |
> | 1.8 | AB | 0001-01-01-00.00.00.000000 |
> | 0.0 | ABCD | 1996-08-15-15.15.15.151515 |
> | -2.4 | ABCDEF | 1996-04-22-23.58.58.123456 |
|
| |
| DML | //
// Demonstration of how to use the various MaxDB functions
//
// User defined procedure (UDP) using recursive expression
// and interting result in a table
DROP TABLE mona.test
//
CREATE TABLE mona.test (row INT,num10 FIXED(10))
//
DROP DBPROC test_data
//
CREATE DBPROC test_data (IN rows INT) AS
VAR row INT;
n10 FIXED(10);
DECLARE c_test_data CURSOR FOR WITH RECURSIVE td (levl,r10) AS
(SELECT 1
, FIXED(RAND(1)*10000000000,10)
FROM dbadmin.dual
UNION ALL
SELECT levl + 1
, FIXED(RAND(levl + 1)*10000000000,10)
FROM td
WHERE levl < 1000
)
SELECT levl,r10
FROM td;
FETCH NEXT c_test_data INTO :row, :n10;
WHILE $RC = 0 DO BEGIN
INSERT INTO mona.test (row,num10) VALUES (:row, :n10);
FETCH NEXT c_test_data INTO :row, :n10;
END;
CLOSE c_test_data;
//
CALL test_data (1000)
//
SELECT * FROM mona.test WHERE rowno < 5
//
|
| Result | > DROP TABLE mona.test
> CREATE TABLE mona.test (row INT,num10 FIXED(10))
> DROP DBPROC test_data
> CREATE DBPROC test_data (IN rows INT) AS VAR row INT; n10 FIXED(10); DECLARE c_test_data CURSOR FOR WITH RECURSIVE td (levl,r10) AS (SELECT 1 , FIXED(RAND(1)*10000000000,10) FROM dbadmin.dual UNION ALL SELECT levl + 1 , FIXED(RAND(levl + 1)*10000000000,10) FROM td WHERE levl < 1000 ) SELECT levl,r10 FROM td; FETCH NEXT c_test_data INTO :row, :n10; WHILE $RC = 0 DO BEGIN INSERT INTO mona.test (row,num10) VALUES (:row, :n10); FETCH NEXT c_test_data INTO :row, :n10; END; CLOSE c_test_data;
> CALL test_data (1000)
> SELECT * FROM mona.test WHERE rowno < 5
> | ROW | NUM10 |
> | -------------- | ---------------- |
> | 1000 | 9130434783 |
> | 999 | 9086956522 |
> | 998 | 9043478261 |
> | 997 | 9000000000 |
|
| |
| DML | //
// Demonstration of how to use the various MaxDB functions
//
// -> server supplied functions
// -> View definition (more examples to generate test data)
// -> commenting SQL
//
CREATE OR REPLACE VIEW mona.test_view AS
SELECT row
/*
,NUM(SUBSTR(CHR(num10),6,2)) AS num02
,NUM(SUBSTR(CHR(num10),5,4)) AS num04
,num10
*/
,TRANSLATE(CHR(num10),'0123456789','qfgHocdeij') AS chr10
,MAKEDATE(
NUM('20' || SUBSTR(CHR(num10),5,2))
,CASE WHEN NUM(SUBSTR(CHR(num10),6,2)) = 0 THEN 1 ELSE NUM(SUBSTR(CHR(num10),6,2)) END
) AS Dat01
,FIXED(num10/100,10,2) AS dec08
FROM mona.test
WHERE row MOD 11 = 0
AND row < 100
//
SELECT dat01 AS "Any Date"
,YEAR(dat01) AS "Year of Any Date"
,MONTH(dat01) AS "Month of Any Date"
,DAY(dat01) AS "Day of Any Date"
,MONTHNAME(dat01) AS "MonthName of Any Date"
,WEEKOFYEAR(dat01) AS "WeekOfYear of Any Date"
,DAYOFYEAR(dat01) AS "DayOfYear of Any Date"
,DAYOFMONTH(dat01) AS "DayOfMonth of Any Date"
,DAYOFWEEK(dat01) AS "DayofWeek of Any Date"
,DAYNAME(dat01) AS "DayName of Any Date"
FROM mona.test_view
//
|
| Result | > CREATE OR REPLACE VIEW mona.test_view AS SELECT row /* ,NUM(SUBSTR(CHR(num10),6,2)) AS num02 ,NUM(SUBSTR(CHR(num10),5,4)) AS num04 ,num10 */ ,TRANSLATE(CHR(num10),'0123456789','qfgHocdeij') AS chr10 ,MAKEDATE( NUM('20' || SUBSTR(CHR(num10),5,2)) ,CASE WHEN NUM(SUBSTR(CHR(num10),6,2)) = 0 THEN 1 ELSE NUM(SUBSTR(CHR(num10),6,2)) END ) AS Dat01 ,FIXED(num10/100,10,2) AS dec08 FROM mona.test WHERE row MOD 11 = 0 AND row < 100
> SELECT dat01 AS "Any Date" ,YEAR(dat01) AS "Year of Any Date" ,MONTH(dat01) AS "Month of Any Date" ,DAY(dat01) AS "Day of Any Date" ,MONTHNAME(dat01) AS "MonthName of Any Date" ,WEEKOFYEAR(dat01) AS "WeekOfYear of Any Date" ,DAYOFYEAR(dat01) AS "DayOfYear of Any Date" ,DAYOFMONTH(dat01) AS "DayOfMonth of Any Date" ,DAYOFWEEK(dat01) AS "DayofWeek of Any Date" ,DAYNAME(dat01) AS "DayName of Any Date" FROM mona.test_view
> | Any Date | Year of Any | Month of Any | Day of Any | MonthName of Any | WeekOfYear of Any | DayOfYear of Any | DayOfMonth of Any | DayofWeek of Any | DayName of Any |
> | ---------- | -------------------------------- | -------------------------------- | -------------------------------- | ---------------------- | -------------------------------- | -------------------------------- | -------------------------------- | -------------------------------- | ---------------------- |
> | 2034-02-16 | 2034 | 2 | 16 | February | 7 | 47 | 16 | 4 | Thursday |
> | 2008-03-26 | 2008 | 3 | 26 | March | 13 | 86 | 26 | 3 | Wednesday |
> | 2082-01-26 | 2082 | 1 | 26 | January | 5 | 26 | 26 | 1 | Monday |
> | 2056-03-05 | 2056 | 3 | 5 | March | 9 | 65 | 5 | 7 | Sunday |
> | 2030-01-04 | 2030 | 1 | 4 | January | 1 | 4 | 4 | 5 | Friday |
> | 2004-02-12 | 2004 | 2 | 12 | February | 7 | 43 | 12 | 4 | Thursday |
> | 2078-03-23 | 2078 | 3 | 23 | March | 12 | 82 | 23 | 3 | Wednesday |
> | 2052-01-21 | 2052 | 1 | 21 | January | 3 | 21 | 21 | 7 | Sunday |
> | 2026-03-01 | 2026 | 3 | 1 | March | 9 | 60 | 1 | 7 | Sunday |
|
| |
| DML | //
// Demonstration of how to use the various MaxDB functions
//
// -> server supplied functions
SELECT TIMESTAMP FROM dual
//
SELECT YEAR(TIMESTAMP) FROM dual
//
SELECT MONTH(TIMESTAMP) FROM dual
//
SELECT MONTHNAME(TIMESTAMP) FROM dual
//
SELECT WEEKOFYEAR(TIMESTAMP),WEEK(TIMESTAMP) FROM dual
//
SELECT DAYOFYEAR(TIMESTAMP) FROM dual
//
SELECT DAYOFMONTH(TIMESTAMP) FROM dual
//
SELECT DAYOFWEEK(TIMESTAMP) FROM dual
//
SELECT DAY(TIMESTAMP) FROM dual
//
SELECT DAYNAME(TIMESTAMP) FROM dual
//
SELECT DATE(TIMESTAMP),TIMESTAMP,UTCDATE FROM dual
//
SELECT TIME(TIMESTAMP) FROM dual
//
SELECT HOUR(TIME(TIMESTAMP)) AS "Hour"
, MINUTE(TIME(TIMESTAMP)) AS "Minute"
, SECOND(TIME(TIMESTAMP)) AS "Second"
, MICROSECOND(TIMESTAMP) AS "Microsecond"
FROM dual
//
SELECT TIMEDIFF(TIMESTAMP,UTCDATE) AS "TimeDif_2_ECT/UTC" FROM dual
//
SELECT HOUR(TIMEDIFF(TIMESTAMP,UTCDATE)) AS "TimeDif_2_ECT/UTC" FROM dual
//
SELECT UTCDATE,TIMEZONE FROM dual
//
SELECT NUM(DATE)-2 FROM dual
//
SELECT SUBDATE(DATE,2) FROM dual
//
SELECT CHAR(DATE,INTERNAL) AS "Character Format"
,NUM(CHAR(DATE,INTERNAL)) AS "Numeric Format"
,CHAR(DATE,EUR) AS "European Format"
,CHAR(DATE,USA) AS "US Format"
,CHAR(DATE,ISO) AS "ISO Format"
FROM DUAL
//
|
| Result | > SELECT TIMESTAMP FROM dual
> | EXPRESSION1 |
> | -------------------------- |
> | 2008-02-17 10:54:47.359000 |
> SELECT YEAR(TIMESTAMP) FROM dual
> | EXPRESSION1 |
> | ------------------ |
> | 2008 |
> SELECT MONTH(TIMESTAMP) FROM dual
> | EXPRESSION1 |
> | ------------------ |
> | 2 |
> SELECT MONTHNAME(TIMESTAMP) FROM dual
> | EXPRESSION1 |
> | ------------ |
> | February |
> SELECT WEEKOFYEAR(TIMESTAMP),WEEK(TIMESTAMP) FROM dual
> | EXPRESSION1 | EXPRESSION2 |
> | ------------------ | ------------------ |
> | 7 | 7 |
> SELECT DAYOFYEAR(TIMESTAMP) FROM dual
> | EXPRESSION1 |
> | ------------------ |
> | 48 |
> SELECT DAYOFMONTH(TIMESTAMP) FROM dual
> | EXPRESSION1 |
> | ------------------ |
> | 17 |
> SELECT DAYOFWEEK(TIMESTAMP) FROM dual
> | EXPRESSION1 |
> | ------------------ |
> | 7 |
> SELECT DAY(TIMESTAMP) FROM dual
> | EXPRESSION1 |
> | ------------------ |
> | 17 |
> SELECT DAYNAME(TIMESTAMP) FROM dual
> | EXPRESSION1 |
> | ------------ |
> | Sunday |
> SELECT DATE(TIMESTAMP),TIMESTAMP,UTCDATE FROM dual
> | EXPRESSION1 | EXPRESSION2 | EXPRESSION3 |
> | ----------- | -------------------------- | -------------------------- |
> | 2008-02-17 | 2008-02-17 10:54:47.359000 | 2008-02-17 09:54:47.359000 |
> SELECT TIME(TIMESTAMP) FROM dual
> | EXPRESSION1 |
> | ----------- |
> | 10:54:47 |
> SELECT HOUR(TIME(TIMESTAMP)) AS "Hour" , MINUTE(TIME(TIMESTAMP)) AS "Minute" , SECOND(TIME(TIMESTAMP)) AS "Second" , MICROSECOND(TIMESTAMP) AS "Microsecond" FROM dual
> | Hour | Minute | Second | Microsecond |
> | -------- | ----------- | ----------- | ------------------ |
> | 10 | 54 | 47 | 359000 |
> SELECT TIMEDIFF(TIMESTAMP,UTCDATE) AS "TimeDif_2_ECT/UTC" FROM dual
> | TimeDif_2_ECT/UTC |
> | ----------------- |
> | 01:00:00 |
> SELECT HOUR(TIMEDIFF(TIMESTAMP,UTCDATE)) AS "TimeDif_2_ECT/UTC" FROM dual
> | TimeDif_2_ECT/UTC |
> | -------------------------- |
> | 1 |
> SELECT UTCDATE,TIMEZONE FROM dual
> | EXPRESSION1 | EXPRESSION2 |
> | -------------------------- | ------------------ |
> | 2008-02-17 09:54:47.359000 | 10000 |
> SELECT NUM(DATE)-2 FROM dual
> | EXPRESSION1 |
> | ---------------------------------------------- |
> | 20080215 |
> SELECT SUBDATE(DATE,2) FROM dual
> | EXPRESSION1 |
> | ----------- |
> | 2008-02-15 |
|
| |
| DML | //
// Demonstration of how to use the various MaxDB DML expressions
//
// -> Get some test data for time series
DROP TABLE time_series
//
CREATE TABLE time_series
(kyy CHAR(03) NOT NULL
,bgn_dt DATE NOT NULL
,end_dt DATE NOT NULL
,CONSTRAINT tsc1 CHECK (kyy <> '')
,CONSTRAINT tsc2 CHECK (bgn_dt <= end_dt)
)
//
INSERT INTO time_series VALUES ('AAA','1995-10-01','1995-10-04')
//
INSERT INTO time_series VALUES ('AAA','1995-10-06','1995-10-06')
//
INSERT INTO time_series VALUES ('AAA','1995-10-07','1995-10-07')
//
INSERT INTO time_series VALUES ('AAA','1995-10-15','1995-10-19')
//
INSERT INTO time_series VALUES ('BBB','1995-10-01','1995-10-01')
//
INSERT INTO time_series VALUES ('BBB','1995-10-03','1995-10-03')
//
// -> correlated subquery examples (find gaps in time series)
SELECT a.kyy
,a.bgn_dt
,a.end_dt
,b.bgn_dt
,b.end_dt
,DATEDIFF(b.bgn_dt,A.end_dt) as diff
FROM time_series a
,time_series b
WHERE a.kyy = b.kyy
AND NUM(a.end_dt) < NUM(b.bgn_dt) - 1
AND NOT EXISTS (SELECT *
FROM time_series z
WHERE z.kyy = a.kyy
AND z.kyy = b.kyy
AND z.bgn_dt > a.bgn_dt
AND z.bgn_dt < b.bgn_dt)
ORDER BY 1,2
//
// -> Put this into a view:
CREATE OR REPLACE VIEW find_gaps_in_time_series (kyy,gapdt,gsize) AS
SELECT a.kyy
,ADDDATE(a.end_dt,1)
,DATEDIFF(b.bgn_dt,a.end_dt) - 1
FROM time_series a
,time_series b
WHERE a.kyy = b.kyy
AND NUM(a.end_dt) < NUM(b.bgn_dt) - 1
AND NOT EXISTS (SELECT *
FROM time_series z
WHERE z.kyy = a.kyy
AND z.kyy = b.kyy
AND z.bgn_dt > a.bgn_dt
AND z.bgn_dt < b.bgn_dt)
//
// -> recursion example tp get each day in gap:
DECLARE c_each_day_in_gap CURSOR FOR WITH RECURSIVE td (kyy,gap_dt,gsize) AS
(SELECT kyy
,gapdt
,gsize
FROM find_gaps_in_time_series
UNION ALL
SELECT kyy
,ADDDATE(gap_dt,1)
,gsize - 1
FROM td
WHERE gsize > 1
)
SELECT *
FROM td
ORDER BY 1,2
//
// -> recursion examples (an example to generate application data (dimension data))
// (time serie to create rows for dimension table dim_time insert:
DECLARE c_dates_4_dim_table CURSOR FOR WITH RECURSIVE dim (id,dim_dt) AS
(
SELECT 1,MAKEDATE(2008,01) FROM dual
UNION ALL
SELECT id+1,ADDDATE(dim_dt,1)
FROM dim
WHERE ADDDATE(dim_dt,1) < '2008-04-01'
)
SELECT id AS id_time
,CHAR(dim_dt,ISO) AS "ISO Date"
,NUM(CHAR(dim_dt,INTERNAL)) AS "Numeric Date"
,YEAR(dim_dt) AS "Year"
,CASE WHEN MONTH(dim_dt) BETWEEN 1 AND 3 THEN 1
WHEN MONTH(dim_dt) BETWEEN 4 AND 6 THEN 2
WHEN MONTH(dim_dt) BETWEEN 7 AND 9 THEN 3
WHEN MONTH(dim_dt) BETWEEN 10 AND 12 THEN 4
END AS "Quarter"
,MONTH(dim_dt) AS "Month"
,WEEK(dim_dt) AS "Week"
,DAYOFYEAR(dim_dt) AS "Day of Year"
,DAYOFWEEK(dim_dt) AS "Day of Week"
,DAY(dim_dt) AS "Day"
,DAYNAME(dim_dt) AS "Dayname"
FROM dim
ORDER BY dim_dt ASC
//
|
| Result | > DROP TABLE time_series
> CREATE TABLE time_series (kyy CHAR(03) NOT NULL ,bgn_dt DATE NOT NULL ,end_dt DATE NOT NULL ,CONSTRAINT tsc1 CHECK (kyy <> '') ,CONSTRAINT tsc2 CHECK (bgn_dt <= end_dt) )
> INSERT INTO time_series VALUES ('AAA','1995-10-01','1995-10-04')
> INSERT INTO time_series VALUES ('AAA','1995-10-06','1995-10-06')
> INSERT INTO time_series VALUES ('AAA','1995-10-07','1995-10-07')
> INSERT INTO time_series VALUES ('AAA','1995-10-15','1995-10-19')
> INSERT INTO time_series VALUES ('BBB','1995-10-01','1995-10-01')
> INSERT INTO time_series VALUES ('BBB','1995-10-03','1995-10-03')
> SELECT a.kyy ,a.bgn_dt ,a.end_dt ,b.bgn_dt ,b.end_dt ,DATEDIFF(b.bgn_dt,A.end_dt) as diff FROM time_series a ,time_series b WHERE a.kyy = b.kyy AND NUM(a.end_dt) < NUM(b.bgn_dt) - 1 AND NOT EXISTS (SELECT * FROM time_series z WHERE z.kyy = a.kyy AND z.kyy = b.kyy AND z.bgn_dt > a.bgn_dt AND z.bgn_dt < b.bgn_dt) ORDER BY 1,2
> | KYY | BGN_DT | END_DT | BGN_DT | END_DT | DIFF |
> | --- | ---------- | ---------- | ---------- | ---------- | ------------ |
> | AAA | 1995-10-01 | 1995-10-04 | 1995-10-06 | 1995-10-06 | 2 |
> | AAA | 1995-10-07 | 1995-10-07 | 1995-10-15 | 1995-10-19 | 8 |
> | BBB | 1995-10-01 | 1995-10-01 | 1995-10-03 | 1995-10-03 | 2 |
> CREATE OR REPLACE VIEW find_gaps_in_time_series (kyy,gapdt,gsize) AS SELECT a.kyy ,ADDDATE(a.end_dt,1) ,DATEDIFF(b.bgn_dt,a.end_dt) - 1 FROM time_series a ,time_series b WHERE a.kyy = b.kyy AND NUM(a.end_dt) < NUM(b.bgn_dt) - 1 AND NOT EXISTS (SELECT * FROM time_series z WHERE z.kyy = a.kyy AND z.kyy = b.kyy AND z.bgn_dt > a.bgn_dt AND z.bgn_dt < b.bgn_dt)
> DECLARE c_each_day_in_gap CURSOR FOR WITH RECURSIVE td (kyy,gap_dt,gsize) AS (SELECT kyy ,gapdt ,gsize FROM find_gaps_in_time_series UNION ALL SELECT kyy ,ADDDATE(gap_dt,1) ,gsize - 1 FROM td WHERE gsize > 1 ) SELECT * FROM td ORDER BY 1,2
> | KYY | GAP_DT | GSIZE |
> | --- | ---------- | ---------------------------------------------- |
> | AAA | 1995-10-05 | 1 |
> | AAA | 1995-10-08 | 7 |
> | AAA | 1995-10-09 | 6 |
> | AAA | 1995-10-10 | 5 |
> | AAA | 1995-10-11 | 4 |
> | AAA | 1995-10-12 | 3 |
> | AAA | 1995-10-13 | 2 |
> | AAA | 1995-10-14 | 1 |
> | BBB | 1995-10-02 | 1 |
> DECLARE c_dates_4_dim_table CURSOR FOR WITH RECURSIVE dim (id,dim_dt) AS ( SELECT 1,MAKEDATE(2008,01) FROM dual UNION ALL SELECT id+1,ADDDATE(dim_dt,1) FROM dim WHERE ADDDATE(dim_dt,1) < '2008-04-01' ) SELECT id AS id_time ,CHAR(dim_dt,ISO) AS "ISO Date" ,NUM(CHAR(dim_dt,INTERNAL)) AS "Numeric Date" ,YEAR(dim_dt) AS "Year" ,CASE WHEN MONTH(dim_dt) BETWEEN 1 AND 3 THEN 1 WHEN MONTH(dim_dt) BETWEEN 4 AND 6 THEN 2 WHEN MONTH(dim_dt) BETWEEN 7 AND 9 THEN 3 WHEN MONTH(dim_dt) BETWEEN 10 AND 12 THEN 4 END AS "Quarter" ,MONTH(dim_dt) AS "Month" ,WEEK(dim_dt) AS "Week" ,DAYOFYEAR(dim_dt) AS "Day of Year" ,DAYOFWEEK(dim_dt) AS "Day of Week" ,DAY(dim_dt) AS "Day" ,DAYNAME(dim_dt) AS "Dayname" FROM dim ORDER BY dim_dt ASC
> | ID_TIME | ISO Date | Numeric Date | Year | Quarter | Month | Week | Day of Year | Day of Week | Day | Dayname |
> | ---------------------------------------------- | ---------- | ---------------------------------------------- | -------- | ------------ | ---------- | -------- | ------------------ | ------------------ | ------- | ------------ |
> | 1 | 2008-01-01 | 20080101 | 2008 | 1 | 1 | 1 | 1 | 2 | 1 | Tuesday |
> | 2 | 2008-01-02 | 20080102 | 2008 | 1 | 1 | 1 | 2 | 3 | 2 | Wednesday |
> | 3 | 2008-01-03 | 20080103 | 2008 | 1 | 1 | 1 | 3 | 4 | 3 | Thursday |
> | 4 | 2008-01-04 | 20080104 | 2008 | 1 | 1 | 1 | 4 | 5 | 4 | Friday |
> | 5 | 2008-01-05 | 20080105 | 2008 | 1 | 1 | 1 | 5 | 6 | 5 | Saturday |
> | 6 | 2008-01-06 | 20080106 | 2008 | 1 | 1 | 1 | 6 | 7 | 6 | Sunday |
> | 7 | 2008-01-07 | 20080107 | 2008 | 1 | 1 | 2 | 7 | 1 | 7 | Monday |
> | 8 | 2008-01-08 | 20080108 | 2008 | 1 | 1 | 2 | 8 | 2 | 8 | Tuesday |
> | 9 | 2008-01-09 | 20080109 | 2008 | 1 | 1 | 2 | 9 | 3 | 9 | Wednesday |
> | 10 | 2008-01-10 | 20080110 | 2008 | 1 | 1 | 2 | 10 | 4 | 10 | Thursday |
> | 11 | 2008-01-11 | 20080111 | 2008 | 1 | 1 | 2 | 11 | 5 | 11 | Friday |
> | 12 | 2008-01-12 | 20080112 | 2008 | 1 | 1 | 2 | 12 | 6 | 12 | Saturday |
> | 13 | 2008-01-13 | 20080113 | 2008 | 1 | 1 | 2 | 13 | 7 | 13 | Sunday |
> | 14 | 2008-01-14 | 20080114 | 2008 | 1 | 1 | 3 | 14 | 1 | 14 | Monday |
> | 15 | 2008-01-15 | 20080115 | 2008 | 1 | 1 | 3 | 15 | 2 | 15 | Tuesday |
> | 16 | 2008-01-16 | 20080116 | 2008 | 1 | 1 | 3 | 16 | 3 | 16 | Wednesday |
> | 17 | 2008-01-17 | 20080117 | 2008 | 1 | 1 | 3 | 17 | 4 | 17 | Thursday |
> | 18 | 2008-01-18 | 20080118 | 2008 | 1 | 1 | 3 | 18 | 5 | 18 | Friday |
> | 19 | 2008-01-19 | 20080119 | 2008 | 1 | 1 | 3 | 19 | 6 | 19 | Saturday |
> | 20 | 2008-01-20 | 20080120 | 2008 | 1 | 1 | 3 | 20 | 7 | 20 | Sunday |
> | 21 | 2008-01-21 | 20080121 | 2008 | 1 | 1 | 4 | 21 | 1 | 21 | Monday |
> | 22 | 2008-01-22 | 20080122 | 2008 | 1 | 1 | 4 | 22 | 2 | 22 | Tuesday |
> | 23 | 2008-01-23 | 20080123 | 2008 | 1 | 1 | 4 | 23 | 3 | 23 | Wednesday |
> | 24 | 2008-01-24 | 20080124 | 2008 | 1 | 1 | 4 | 24 | 4 | 24 | Thursday |
> | 25 | 2008-01-25 | 20080125 | 2008 | 1 | 1 | 4 | 25 | 5 | 25 | Friday |
> | 26 | 2008-01-26 | 20080126 | 2008 | 1 | 1 | 4 | 26 | 6 | 26 | Saturday |
> | 27 | 2008-01-27 | 20080127 | 2008 | 1 | 1 | 4 | 27 | 7 | 27 | Sunday |
> | 28 | 2008-01-28 | 20080128 | 2008 | 1 | 1 | 5 | 28 | 1 | 28 | Monday |
> | 29 | 2008-01-29 | 20080129 | 2008 | 1 | 1 | 5 | 29 | 2 | 29 | Tuesday |
> | 30 | 2008-01-30 | 20080130 | 2008 | 1 | 1 | 5 | 30 | 3 | 30 | Wednesday |
> | 31 | 2008-01-31 | 20080131 | 2008 | 1 | 1 | 5 | 31 | 4 | 31 | Thursday |
> | 32 | 2008-02-01 | 20080201 | 2008 | 1 | 2 | 5 | 32 | 5 | 1 | Friday |
> | 33 | 2008-02-02 | 20080202 | 2008 | 1 | 2 | 5 | 33 | 6 | 2 | Saturday |
> | 34 | 2008-02-03 | 20080203 | 2008 | 1 | 2 | 5 | 34 | 7 | 3 | Sunday |
> | 35 | 2008-02-04 | 20080204 | 2008 | 1 | 2 | 6 | 35 | 1 | 4 | Monday |
> | 36 | 2008-02-05 | 20080205 | 2008 | 1 | 2 | 6 | 36 | 2 | 5 | Tuesday |
> | 37 | 2008-02-06 | 20080206 | 2008 | 1 | 2 | 6 | 37 | 3 | 6 | Wednesday |
> | 38 | 2008-02-07 | 20080207 | 2008 | 1 | 2 | 6 | 38 | 4 | 7 | Thursday |
> | 39 | 2008-02-08 | 20080208 | 2008 | 1 | 2 | 6 | 39 | 5 | 8 | Friday |
> | 40 | 2008-02-09 | 20080209 | 2008 | 1 | 2 | 6 | 40 | 6 | 9 | Saturday |
> | 41 | 2008-02-10 | 20080210 | 2008 | 1 | 2 | 6 | 41 | 7 | 10 | Sunday |
> | 42 | 2008-02-11 | 20080211 | 2008 | 1 | 2 | 7 | 42 | 1 | 11 | Monday |
> | 43 | 2008-02-12 | 20080212 | 2008 | 1 | 2 | 7 | 43 | 2 | 12 | Tuesday |
> | 44 | 2008-02-13 | 20080213 | 2008 | 1 | 2 | 7 | 44 | 3 | 13 | Wednesday |
> | 45 | 2008-02-14 | 20080214 | 2008 | 1 | 2 | 7 | 45 | 4 | 14 | Thursday |
> | 46 | 2008-02-15 | 20080215 | 2008 | 1 | 2 | 7 | 46 | 5 | 15 | Friday |
> | 47 | 2008-02-16 | 20080216 | 2008 | 1 | 2 | 7 | 47 | 6 | 16 | Saturday |
> | 48 | 2008-02-17 | 20080217 | 2008 | 1 | 2 | 7 | 48 | 7 | 17 | Sunday |
> | 49 | 2008-02-18 | 20080218 | 2008 | 1 | 2 | 8 | 49 | 1 | 18 | Monday |
> | 50 | 2008-02-19 | 20080219 | 2008 | 1 | 2 | 8 | 50 | 2 | 19 | Tuesday |
> | 51 | 2008-02-20 | 20080220 | 2008 | 1 | 2 | 8 | 51 | 3 | 20 | Wednesday |
> | 52 | 2008-02-21 | 20080221 | 2008 | 1 | 2 | 8 | 52 | 4 | 21 | Thursday |
> | 53 | 2008-02-22 | 20080222 | 2008 | 1 | 2 | 8 | 53 | 5 | 22 | Friday |
> | 54 | 2008-02-23 | 20080223 | 2008 | 1 | 2 | 8 | 54 | 6 | 23 | Saturday |
> | 55 | 2008-02-24 | 20080224 | 2008 | 1 | 2 | 8 | 55 | 7 | 24 | Sunday |
> | 56 | 2008-02-25 | 20080225 | 2008 | 1 | 2 | 9 | 56 | 1 | 25 | Monday |
> | 57 | 2008-02-26 | 20080226 | 2008 | 1 | 2 | 9 | 57 | 2 | 26 | Tuesday |
> | 58 | 2008-02-27 | 20080227 | 2008 | 1 | 2 | 9 | 58 | 3 | 27 | Wednesday |
> | 59 | 2008-02-28 | 20080228 | 2008 | 1 | 2 | 9 | 59 | 4 | 28 | Thursday |
> | 60 | 2008-02-29 | 20080229 | 2008 | 1 | 2 | 9 | 60 | 5 | 29 | Friday |
> | 61 | 2008-03-01 | 20080301 | 2008 | 1 | 3 | 9 | 61 | 6 | 1 | Saturday |
> | 62 | 2008-03-02 | 20080302 | 2008 | 1 | 3 | 9 | 62 | 7 | 2 | Sunday |
> | 63 | 2008-03-03 | 20080303 | 2008 | 1 | 3 | 10 | 63 | 1 | 3 | Monday |
> | 64 | 2008-03-04 | 20080304 | 2008 | 1 | 3 | 10 | 64 | 2 | 4 | Tuesday |
> | 65 | 2008-03-05 | 20080305 | 2008 | 1 | 3 | 10 | 65 | 3 | 5 | Wednesday |
> | 66 | 2008-03-06 | 20080306 | 2008 | 1 | 3 | 10 | 66 | 4 | 6 | Thursday |
> | 67 | 2008-03-07 | 20080307 | 2008 | 1 | 3 | 10 | 67 | 5 | 7 | Friday |
> | 68 | 2008-03-08 | 20080308 | 2008 | 1 | 3 | 10 | 68 | 6 | 8 | Saturday |
> | 69 | 2008-03-09 | 20080309 | 2008 | 1 | 3 | 10 | 69 | 7 | 9 | Sunday |
> | 70 | 2008-03-10 | 20080310 | 2008 | 1 | 3 | 11 | 70 | 1 | 10 | Monday |
> | 71 | 2008-03-11 | 20080311 | 2008 | 1 | 3 | 11 | 71 | 2 | 11 | Tuesday |
> | 72 | 2008-03-12 | 20080312 | 2008 | 1 | 3 | 11 | 72 | 3 | 12 | Wednesday |
> | 73 | 2008-03-13 | 20080313 | 2008 | 1 | 3 | 11 | 73 | 4 | 13 | Thursday |
> | 74 | 2008-03-14 | 20080314 | 2008 | 1 | 3 | 11 | 74 | 5 | 14 | Friday |
> | 75 | 2008-03-15 | 20080315 | 2008 | 1 | 3 | 11 | 75 | 6 | 15 | Saturday |
> | 76 | 2008-03-16 | 20080316 | 2008 | 1 | 3 | 11 | 76 | 7 | 16 | Sunday |
> | 77 | 2008-03-17 | 20080317 | 2008 | 1 | 3 | 12 | 77 | 1 | 17 | Monday |
> | 78 | 2008-03-18 | 20080318 | 2008 | 1 | 3 | 12 | 78 | 2 | 18 | Tuesday |
> | 79 | 2008-03-19 | 20080319 | 2008 | 1 | 3 | 12 | 79 | 3 | 19 | Wednesday |
> | 80 | 2008-03-20 | 20080320 | 2008 | 1 | 3 | 12 | 80 | 4 | 20 | Thursday |
> | 81 | 2008-03-21 | 20080321 | 2008 | 1 | 3 | 12 | 81 | 5 | 21 | Friday |
> | 82 | 2008-03-22 | 20080322 | 2008 | 1 | 3 | 12 | 82 | 6 | 22 | Saturday |
> | 83 | 2008-03-23 | 20080323 | 2008 | 1 | 3 | 12 | 83 | 7 | 23 | Sunday |
> | 84 | 2008-03-24 | 20080324 | 2008 | 1 | 3 | 13 | 84 | 1 | 24 | Monday |
> | 85 | 2008-03-25 | 20080325 | 2008 | 1 | 3 | 13 | 85 | 2 | 25 | Tuesday |
> | 86 | 2008-03-26 | 20080326 | 2008 | 1 | 3 | 13 | 86 | 3 | 26 | Wednesday |
> | 87 | 2008-03-27 | 20080327 | 2008 | 1 | 3 | 13 | 87 | 4 | 27 | Thursday |
> | 88 | 2008-03-28 | 20080328 | 2008 | 1 | 3 | 13 | 88 | 5 | 28 | Friday |
> | 89 | 2008-03-29 | 20080329 | 2008 | 1 | 3 | 13 | 89 | 6 | 29 | Saturday |
> | 90 | 2008-03-30 | 20080330 | 2008 | 1 | 3 | 13 | 90 | 7 | 30 | Sunday |
> | 91 | 2008-03-31 | 20080331 | 2008 | 1 | 3 | 14 | 91 | 1 | 31 | Monday |
|
| |