| >> |
| >>create table t053t1 (a date not null, b timestamp not null); |
| |
| --- SQL operation complete. |
| >>create table t053t2 (a int, b double precision); |
| |
| --- SQL operation complete. |
| >> |
| >>insert into t053t1 values (date '1998-02-04', timestamp '1997-06-03:10:10:10.00000'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>insert into t053t2 values (45, 45); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>?section dml |
| >>select dayname(a), dayname(b) from t053t1; |
| |
| (EXPR) (EXPR) |
| --------- --------- |
| |
| Wednesday Tuesday |
| |
| --- 1 row(s) selected. |
| >>select monthname(a) from t053t1 where monthname(b) = 'June'; |
| |
| (EXPR) |
| --------- |
| |
| February |
| |
| --- 1 row(s) selected. |
| >> |
| >>select dayofyear(a), dayofyear(b) from t053t1; |
| |
| (EXPR) (EXPR) |
| -------------------- -------------------- |
| |
| 35 154 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select week(a), week(b) from t053t1; |
| |
| (EXPR) (EXPR) |
| -------------------- -------------------- |
| |
| 6 23 |
| |
| --- 1 row(s) selected. |
| >> |
| >>select quarter(a), quarter(b) from t053t1; |
| |
| (EXPR) (EXPR) |
| ------ ------ |
| |
| 1 2 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- more date-time function tests |
| >>insert into t053t1 values (date '1901-10-10', |
| +> timestamp '1901-10-10 23:15:00.300000'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t053t1 values (date '1999-12-31', |
| +> timestamp '1999-12-31 23:59:59.999999'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t053t1 values (date '2000-01-01', |
| +> timestamp '2000-01-01 23:59:59.999999'); |
| |
| --- 1 row(s) inserted. |
| >>insert into t053t1 values (date '1998-02-04', -- a duplicate row |
| +> timestamp '1997-06-03:10:10:10.00000'); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>-- check that a special rounding mode does not affect date-time calculations |
| >>control query default ROUNDING_MODE '2'; |
| |
| --- SQL operation complete. |
| >> |
| >>select a,b,DATEDIFF(DAY,a,b) as DIFF,WEEK(b) as WEEK, |
| +> DATE_TRUNC('SECOND', b) as TRUNC_SECOND, |
| +> DATE_TRUNC('MINUTE', b) as TRUNC_MINUTE, |
| +> DATE_TRUNC('HOUR', b) as TRUNC_HOUR, |
| +> DATE_TRUNC('DAY', b) as TRUNC_DAY, |
| +> DATE_TRUNC('DECADE', b) as TRUNC_DECADE |
| +> from t053t1 |
| +> group by a,b |
| +> having DATE_PART('YEAR',b) in (1901,1997,1999,2000); |
| |
| A B DIFF WEEK TRUNC_SECOND TRUNC_MINUTE TRUNC_HOUR TRUNC_DAY TRUNC_DECADE |
| ---------- -------------------------- ----------- -------------------- -------------------------- -------------------------- -------------------------- -------------------------- -------------------------- |
| |
| 1998-02-04 1997-06-03 10:10:10.000000 -246 23 1997-06-03 10:10:10.000000 1997-06-03 10:10:00.000000 1997-06-03 10:00:00.000000 1997-06-03 00:00:00.000000 1990-01-01 00:00:00.000000 |
| 1901-10-10 1901-10-10 23:15:00.300000 0 41 1901-10-10 23:15:00.000000 1901-10-10 23:15:00.000000 1901-10-10 23:00:00.000000 1901-10-10 00:00:00.000000 1900-01-01 00:00:00.000000 |
| 1999-12-31 1999-12-31 23:59:59.999999 0 53 1999-12-31 23:59:59.000000 1999-12-31 23:59:00.000000 1999-12-31 23:00:00.000000 1999-12-31 00:00:00.000000 1990-01-01 00:00:00.000000 |
| 2000-01-01 2000-01-01 23:59:59.999999 0 1 2000-01-01 23:59:59.000000 2000-01-01 23:59:00.000000 2000-01-01 23:00:00.000000 2000-01-01 00:00:00.000000 2000-01-01 00:00:00.000000 |
| |
| --- 4 row(s) selected. |
| >> |
| >>-- should return one row |
| >>SELECT b, WEEK(b) from t053t1 where week(b) = 41; |
| |
| B (EXPR) |
| -------------------------- -------------------- |
| |
| 1901-10-10 23:15:00.300000 41 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- should return two rows |
| >>SELECT b, DATEDIFF(WEEK, b, timestamp '2000-12-31 23:59:59.999999') |
| +> from t053t1 where |
| +> DATEDIFF(WEEK, b, timestamp '2000-12-31 23:59:59.999999') = 53; |
| |
| B (EXPR) |
| -------------------------- ----------- |
| |
| 1999-12-31 23:59:59.999999 53 |
| 2000-01-01 23:59:59.999999 53 |
| |
| --- 2 row(s) selected. |
| >> |
| >>-- division in an argument within WEEK(...) should follow the rounding mode |
| >>control query default ROUNDING_MODE '1'; |
| |
| --- SQL operation complete. |
| >>select WEEK(b+175499),WEEK(b+CAST(350999/2 as int)) from t053t1 |
| +> where a = date '1901-10-10'; |
| |
| (EXPR) (EXPR) |
| -------------------- -------------------- |
| |
| 41 42 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- Math functions |
| >>select ACOS(a), ASIN(a), ATAN(a), COS(a), COSH(a), SIN(a), SINH(a), TAN(a), TANH(a) from t053t2; |
| |
| *** ERROR[8428] The argument to function ACOS is not valid. |
| |
| --- 0 row(s) selected. |
| >> |
| >> |
| >>-- Subtraction between two datetime columns containing a null value |
| >>-- no longer returns error. |
| >>-- Case: 10-980825-1690 |
| >>create table t053d (a date, b date); |
| |
| --- SQL operation complete. |
| >>insert into t053d values (date '1998-09-04', null); |
| |
| --- 1 row(s) inserted. |
| >>select a - b from t053d; |
| |
| (EXPR) |
| ------------- |
| |
| ? |
| |
| --- 1 row(s) selected. |
| >>select (a - b) day from t053d; |
| |
| (EXPR) |
| ------ |
| |
| ? |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>-- Parser now supportes the syntax DAY(<value-expression>) |
| >>-- Case: 10-980901-0398 |
| >>select day(date '1998-09-04') from (values(1)) x(a); |
| |
| (EXPR) |
| ------ |
| |
| 4 |
| |
| --- 1 row(s) selected. |
| >> |
| >>-- An error is no longer returned if the start position in a SUBSTRING |
| >>-- function exceeds the max length. |
| >>-- Case: 10-980901-0431 |
| >>select substring('aaa' from 30 for 2) from (values(1)) x(a); |
| |
| (EXPR) |
| ------ |
| |
| |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>-- The CHAR function no longer returns error on certain valid values. |
| >>-- Case: 10-980902-1131 |
| >>select ascii(char(254)) from (values(1)) x(a); |
| |
| (EXPR) |
| ---------- |
| |
| 254 |
| |
| --- 1 row(s) selected. |
| >> |
| >> |
| >>-- The REPEAT function was not type propagating its operand. It does |
| >>-- so now. |
| >>-- Case: 10-980902-1225 |
| >> |
| >> |
| >>-- should return error. |
| >>select replace('ab', 'ab', current_date) from (values(1)) x(a); |
| |
| *** ERROR[4064] The operands of function REPLACE must be compatible character data types (that is, of the same character set). |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>-- test fix to genesis case 10-030220-1214. This used to cause an NSK cpu halt. |
| >>insert into t053t2(b) values( |
| +>162769514214177976464774676766466427976779777977979777789879764347467647767649 |
| +>); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>log; |
