test/sql/ScalarFunction/exp.test007.ksh

   1 echo Create table t1(f1 date, f2 date);
   2 Statement Executed
   3 Statement Executed: Rows Affected = 1
   4 Statement Executed: Rows Affected = 1
   5 Statement Executed: Rows Affected = 1
   6 Statement Executed: Rows Affected = 1
   7 Statement Executed: Rows Affected = 1
   8 echo select * from t1;
   9 ---------------------------------------------------------
  10         t1.f1   t1.f2
  11 ---------------------------------------------------------
  12         2001/1/1        2001/1/1
  13         2002/1/1        2001/2/1
  14         2003/1/1        2001/3/1
  15         2004/1/1        2001/4/1
  16         2005/1/1        2001/5/1
  17
  18 echo select * from t1 where date_sub(f2 interval  6 month)  = '2000-09-01';
  19 ---------------------------------------------------------
  20         t1.f1   t1.f2
  21 ---------------------------------------------------------
  22         2003/1/1        2001/3/1
  23
  24 echo select * from t1 where date_sub(f2 interval  6 month) <= '2000-09-01';
  25 ---------------------------------------------------------
  26         t1.f1   t1.f2
  27 ---------------------------------------------------------
  28         2001/1/1        2001/1/1
  29         2002/1/1        2001/2/1
  30         2003/1/1        2001/3/1
  31
  32 echo select * from t1 where date_sub(f2 interval  6 month) >= '2000-09-01';
  33 ---------------------------------------------------------
  34         t1.f1   t1.f2
  35 ---------------------------------------------------------
  36         2003/1/1        2001/3/1
  37         2004/1/1        2001/4/1
  38         2005/1/1        2001/5/1
  39
  40 echo select * from t1 where date_sub(f2 interval 18 month)  = '1999-09-01';
  41 ---------------------------------------------------------
  42         t1.f1   t1.f2
  43 ---------------------------------------------------------
  44         2003/1/1        2001/3/1
  45
  46 echo select * from t1 where date_sub(f2 interval 18 month) <= '1999-09-01';
  47 ---------------------------------------------------------
  48         t1.f1   t1.f2
  49 ---------------------------------------------------------
  50         2001/1/1        2001/1/1
  51         2002/1/1        2001/2/1
  52         2003/1/1        2001/3/1
  53
  54 echo select * from t1 where date_sub(f2 interval 18 month) >= '1999-09-01';
  55 ---------------------------------------------------------
  56         t1.f1   t1.f2
  57 ---------------------------------------------------------
  58         2003/1/1        2001/3/1
  59         2004/1/1        2001/4/1
  60         2005/1/1        2001/5/1
  61
  62 Statement Executed