| 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 |
|